SQL Web Mentor

FOR XML with SQL queries

2011-06-21T00:15:00.000-07:00

id test
----------- -----------
1 prk
2 prk1
3 prk2

select * from dbo.aaaa1 for xml raw

Output:

select * from dbo.aaaa1 for XML AUTO

Output:

select * from dbo.aaaa1 for xml auto,elements

Output:

1prk2prk1
3prk2

select * from dbo.aaaa1 for XML AUTO ,Elements XSINIL

Output:
Give same o/p as above but maps null value also. The ELEMENTS directive provides an XSINIL option to map NULL values to an element with an attribute xsi:nil="true".

1prk2prk1
3prk2

2011-03-15T12:49:00.000-07:00

Overview:

When deploying applications to a production server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Most of the code / business logic resides in Stored procedures.
Below is a mechanism to secure the source code of Stored procedure:
--------------------------
CREATE PROCEDURE [test]
WITH ENCRYPTION
AS
BEGIN
SELECT 'test'
END
--------------------------

1) Any one will not be able to open the procedure in Enterprise Manager's GUI. He will receive the following error:

-----------------------------

Microsoft SQL-DMO
Error 20585: [SQL-DMO]
/******
Encrypted object is not transferable,
and script can not be generated.
******/

-----------------------------

2) You can not get source code with sp_helptext [test]

----------------------------

2 ways I can find the code.

1) Peep in SQL profiler

2) Create new stored procedure.

How to Stop SQL Injection

2010-11-12T10:11:00.000-08:00

1) Create User WebUser
2) Make it db_Owner for the application database
3) Execute below SQL statements

use [master]
GO

DENY SELECT ON [INFORMATION_SCHEMA].[TABLES] TO [Public]

DENY SELECT ON [INFORMATION_SCHEMA].[COLUMNS] TO [Public]

DENY SELECT ON [INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE] TO [Public]

DENY SELECT ON [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] TO [Public]

DENY SELECT ON [INFORMATION_SCHEMA].[COLUMN_PRIVILEGES] TO [Public]
GO

Use [SQLInjection]
GO

DENY SELECT ON [sys].[columns] TO [test]

DENY SELECT ON [sys].[tables] TO [test]

DENY SELECT ON [sys].[syscolumns] TO [test]

DENY SELECT ON [sys].[sysobjects] TO [test]

DENY SELECT ON [sys].[objects] TO [test]

DENY SELECT ON [sys].[syscomments] TO [test]
GO

Script to find out the SQL infection

2010-11-12T10:09:00.000-08:00

DECLARE @T varchar(255),@C varchar(4000)
DECLARE Table_Cursor CURSOR FOR
select a.name,b.name from sysobjects a,syscolumns b
where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231
or b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN

exec('Select * from ['+@T+'] where ['+@C+'] like ''%title%''')

FETCH NEXT FROM
Table_Cursor INTO @T,@C END CLOSE
Table_Cursor DEALLOCATE Table_Cursor

Handle Fatal Error in SQL using DOT NET Try Catch block

2010-08-13T12:27:00.001-07:00

Stored Procedure with fatal error

tempPK has one column with datatype = int
==============================================
CREATE PROCEDURE [dbo].[spError]
AS
BEGIN

Begin Tran
--Select 1/0

--execute('insert into tempPK select ''s''')
execute('insert into tempPK select ''fg''')
execute('insert into tempPK select 4')
Commit Tran
print 'Success'
END

======================================================
Dot Net Calling Method
try
{
SqlConnection sCon = new SqlConnection();
sCon.ConnectionString = "Database=xyz;Server=10.62;User Id=sa;Password=#12#";
sCon.Open();
SqlCommand sCom = new SqlCommand();
sCom.Connection = sCon;
sCom.CommandText = "Exec spError";
sCom.ExecuteNonQuery();
}
catch (Exception ex)
{
if(ex.Message.Substring(ex.Message.Length-7) == "Success")
MessageBox.Show("No Fatal Error");
else
MessageBox.Show("Fatal Error");
}
======================================================

Get Non Fatal Errors from SQL in DOT NET CATCH block

2010-08-13T12:26:00.001-07:00

Stored Procedure having errors within
=======================
CREATE PROCEDURE spError
AS
BEGIN

Begin Tran
Select 1/0
execute('insert into te mpPK select 1')
execute('insert into tempPK select 3')
Commit Tran
END
GO
=======================
Application Code
try
{
SqlConnection sCon = new SqlConnection();
sCon.ConnectionString = "Database=xyz;Server=11.62;User Id=sa;Password=klk";
sCon.Open();
SqlCommand sCom = new SqlCommand();
sCom.Connection = sCon;
sCom.CommandText = "Exec spError";
sCom.ExecuteNonQuery();
}
catch (Exception ex)
{
textBox1.Text = ex.Message;
}
=======================
Errors captured
Divide by zero error encountered.
Incorrect syntax near 'mpPK'.

Getting XML from SQL

2010-07-31T07:05:00.000-07:00

--------------------------------------------------------------------
SELECT * FROM tempPK
Result:
c
---
211
311
--------------------------------------------------------------------
SELECT * FROM tempPK FOR XML AUTO, TYPE
Result:

--------------------------------------------------------------------
SELECT * FROM tempPK FOR XML AUTO, TYPE
Result:
211311
--------------------------------------------------------------------
SELECT * FROM tempPK FOR XML RAW('Cust'), ELEMENTS
Result:
134
--------------------------------------------------------------------
Add Null value.

SELECT * FROM tempPK
Result:
c
---
211
NULL
311

SELECT * FROM tempPK where c = null FOR XML AUTO, ELEMENTS

Result:
No xml will be generated.

SELECT * FROM tempPK FOR XML AUTO, ELEMENTS XSINIL

SAVE TRANSACTION- Partial Commit,Rollback

2010-07-31T06:28:00.000-07:00

Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.
----------------------------------------------------------
BEGIN TRAN
--------------------Processing 1--------
SAVE TRANSACTION sp1
Insert into tempPK select 111
Select 1/0
if @@Error<>0
rollback Tran sp1
--------------------Processing 2--------
SAVE TRANSACTION sp2
Insert into tempPK select 211
--Select 1/0
if @@Error<>0
rollback Tran sp2
--------------------Processing 3--------
SAVE TRANSACTION sp3
Insert into tempPK select 311
--Select 1/0
if @@Error<>0
rollback Tran sp3

commit tran
--------------------------------
OutPut:
Proessing 2 and 3 commited
Proessing 1 will be rollback

Transaction- BEGIN, COMMIT,ROLLBACK

2010-07-31T06:25:00.000-07:00

Begin Trans t
// Do processing

if @@Error<>0
Rollback Tran t
else
Commit Tran t

Error Handling- Use GOTO

2010-07-31T06:14:00.000-07:00

-------------------
//processing
Select 1/0
if(@@Error<>0)
GOTO Err_Handler
-------------------
Err_Handler:
BEGIN
//Log error
//Rollback transaction
END
-------------------
SP_OUT:
BEGIN
// Finalize code
END
-------------------

Get Error Messages using @@Error

2010-07-31T06:13:00.000-07:00

CREATE PROCEDURE [dbo].[sp_GetErrorMessages]
@errorId integer,
@strErrorMessage nvarchar(1000) OUTPUT
AS
/*
CREATED BY :
CREATED ON : 1 JULY 2010
PURPOSE : Getting Error message based on errorcode
INPUT : @@errorId - It is an error number
OUTPUT : @@@strErrorMessage - It is an error message
MODIFIED BY :
MODIFIED ON : 1 JULY 2010

Description :
Error number can be found out using @@Error.
1033 = Language id for english
*/
SELECT
@strErrorMessage = description
FROM
master.sys.sysmessages
WHERE
error = @errorId and
msglangid = 1033;

@@ERROR

2010-07-31T05:56:00.000-07:00

@@ERROR gets an error number when every Transact-SQL statement completes, process @@ERROR in one of two ways:

1) Test or use @@ERROR immediately after the Transact-SQL statement.
2) Save @@ERROR in an integer variable immediately after the Transact-SQL statement completes. The value of the variable can be used later.

@@ROWCOUNT

2010-07-31T05:41:00.000-07:00

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

1) Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
2) Preserve @@ROWCOUNT from the previous statement execution.
3) Reset @@ROWCOUNT to 0 but do not return the value to the client.

a)Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

b)Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.

c)Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

d)DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

e)EXECUTE statements preserve the previous @@ROWCOUNT.

f)Statements such as USE, SET

Data Transfer and table creation

2010-07-31T05:28:00.000-07:00

1) Transfering data from one table to another with same schema

INSERT INTO temp SELECT * from dbo.tempPK

2) Create temporary table with same schema and Transfering data

Select * into #temp from dbo.tempPK

3) Create physical table with same schema and Transfering data

Select * into temp from dbo.tempPK

4) Create physical table with same schema but no data Transfer

Select * into temp from dbo.tempPK where 1=2

COALESCE- Convert Row values to Comma Separated value

2010-07-31T05:27:00.001-07:00

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(c AS varchar(5)) FROM Employee
Select @EmployeeList

Table Data Script Generator

2010-07-30T13:22:00.000-07:00

CREATE PROCEDURE sp_Table_DataScript_Generator
(@tableName varchar(100)) as

--Declare a cursor to retrieve column specific information
--for the specified table

DECLARE cursorCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns
WHERE table_name = @tableName

OPEN cursorCol
DECLARE @string nvarchar(4000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(4000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns

SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursorCol INTO @colName,@dataType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found.'
close cursorCol
deallocate cursorCol
return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @stringData=@stringData+'''''''''+ RTRIM(isnull('+@colName+',''''))+'''''',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+
isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
SET @stringData=@stringData+'''convert(datetime,''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast(convert(varbinary,'+@colName+')
as varchar(6)),''0'')+'''''',''+'
END
ELSE --Rest data type is int,bit,numeric,decimal
BEGIN
SET @stringData=@stringData+'''''''''+
isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursorCol INTO @colName,@dataType
END

DECLARE @Query nvarchar(4000) -- provide for the whole query, you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')
VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''
FROM '+@tableName
exec sp_executesql @query --load and run the built query

CLOSE cursorCol
DEALLOCATE cursorCol

Stored Procedures vs. User Defined Functions

2010-07-30T12:10:00.000-07:00

Stored Procedures vs. User Defined Functions in SQL Server

1) SQL Server user-defined functions and stored procedures offer similar functionality. 2) Both allow you to create bundles of SQL statements that are stored on the server for future use.

Benefit

1) you can save programming time by Reusing code from one program to another,
2) cutting down program development time.
3) Hiding the SQL details,
4) Allowing database developers to work with SQL and application developers to deal only in application programming.
5) Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications.

Difference

1) Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
2) Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables.
3) Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
4) You can not write exec('Insert Into ...') in UDF.
5) you couldn't do updates in a user defined function.

Group By Clause

2010-07-30T11:39:00.000-07:00

SQL GROUP BY Syntax

Defination

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Example

Group by on all table.

SELECT CustomerName,SUM(CustomerOrder) FROM CustomerOrder
GROUP BY CustomerName

Group by filtered table.

SELECT CustomerName,SUM(CustomerOrder) FROM CustomerOrder
where CustomerName like 'JO%'
GROUP BY CustomerName

Same result but using Having.

SELECT CustomerName,SUM(CustomerOrder) FROM CustomerOrder
GROUP BY CustomerName
having CustomerName like 'JO%'

Using Having to query on aggregate.

SELECT CustomerName,SUM(CustomerOrder) FROM CustomerOrder
GROUP BY CustomerName
having sum(CustomerOrder) > 4

Group By more than 2 column.

SELECT CustomerName,OrderDate,SUM(CustomerOrder) FROM CustomerOrder
GROUP BY CustomerName,OrderDate

How to avoid cursor

2010-07-04T11:24:00.000-07:00

Using the SQL WHILE loop

-- Create a temporary table, note the IDENTITY
-- column that will be used to loop through
-- the rows of this table
CREATE TABLE #tmpTable (
RowID int IDENTITY(1, 1),
CustomerID int,
FirstName varchar(30),
LastName varchar(30)
)

DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)

-- Insert the resultset we want to loop through
-- into the temporary table
INSERT INTO #tmpTable (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

-- loop through all records in the temporary table
-- using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
--Do Processing
SET @RowCount = @RowCount + 1
END

-- drop the temporary table
DROP TABLE #tmpTable

SELECT - FROM - WHERE Query

2010-03-20T04:09:00.000-07:00

A SQL SELECT statement can be broken down into numerous elements, each beginning with a keyword. Although it is not necessary, common convention is to write these keywords in all capital letters. In this article, we will focus on the most fundamental and common elements of a SELECT statement, namely

• SELECT
• FROM
• WHERE
• ORDER BY

If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like

SELECT * FROM Employees
-------------------------------
Get only specific columns data for all of the rows in the table:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees

-------------------------------

The WHERE Clause

--Filter Data for city "London"

SELECT * FROM Employees WHERE City = 'London'

-------------------------------

---The employees who do not live in London

SELECT Employee,* FROM Employees WHERE City <> 'London'

-------------------------------

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE HireDate >= '1-july-1993'

-------------------------------

SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE (HireDate >= '1-june-1992') AND (HireDate <= '15-december-1993')

-------------------------------
Note that SQL also has a special BETWEEN operator that checks to see if a value is between two values (including equality on both ends). This allows us to rewrite the previous query as

SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE HireDate BETWEEN '1-june-1992' AND '15-december-1993'

-------------------------------
We could also use the NOT operator, to fetch those rows that are not between the specified dates:

SELECT EmployeeID, FirstName, LastName, HireDate, CityFROM EmployeesWHERE HireDate NOT BETWEEN '1-june-1992' AND '15-december-1993'
-------------------------------
What if we want to check if a column value is equal to more than one value? If it is only 2 values, then it is easy enough to test for each of those values, combining them with the OR operator and writing something like

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE City = 'London' OR City = 'Seattle'
-------------------------------
However, if there are three, four, or more values that we want to compare against, the above approach quickly becomes messy. In such cases, we can use the IN operator to test against a set of values. If we wanted to see if the City was either Seattle, Tacoma, or Redmond, we would write

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE City IN ('Seattle', 'Tacoma', 'Redmond')
-------------------------------
As with the BETWEEN operator, here too we can reverse the results obtained and query for those rows where City is not in the specified list:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE City NOT IN ('Seattle', 'Tacoma', 'Redmond')
-------------------------------

Finally, the LIKE operator allows us to perform basic pattern-matching using wildcard characters. For Microsoft SQL Server, the wildcard characters are defined as follows:

Wildcard Description

_ (underscore) = matches any single character

% = matches a string of one or more characters

[ ] = matches any single character within the specified range (e.g. [a-f]) or set (e.g. [abcdef]).

[^] = matches any single character not within the specified range (e.g. [^a-f]) or set (e.g. [^abcdef]).

A few examples should help clarify these rules.

WHERE FirstName LIKE '_im' finds all three-letter first names that end with 'im' (e.g. Jim, Tim).

WHERE LastName LIKE '%stein' finds all employees whose last name ends with 'stein'

WHERE LastName LIKE '%stein%' finds all employees whose last name includes 'stein' anywhere in the name.

WHERE FirstName LIKE '[JT]im' finds three-letter first names that end with 'im' and begin with either 'J' or 'T' (that is, only Jim and Tim)

WHERE LastName LIKE 'm[^c]%' finds all last names beginning with 'm' where the following (second) letter is not 'c'.

Here too, we can opt to use the NOT operator: to find all of the employees whose first name does not start with 'M' or 'A', we would write

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM EmployeesWHERE (FirstName NOT LIKE 'M%') AND (FirstName NOT LIKE 'A%')
-------------------------------
The ORDER BY Clause

By default, the sort order for a column is ascending (from lowest value to highest value), as shown below for the previous query:

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees ORDER BY City

-------------------------------

--Order by 2 columns

SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM Employees ORDER BY Country, City DESC

-------------------------------

--Order by 2 columns but in different order

SELECT EmployeeID, FirstName, LastName, HireDate, Country, City FROM EmployeesORDER BY Country ASC, City DESC

-------------------------------

Deleting duplicate rows from a table

2010-03-19T08:23:00.000-07:00

A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates.
This can be achieved in tow ways :-

(a) Using a temporary table.

CREATE TABLE Employee_Test1
(Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2))

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

Step 1:Create a temporary table from the main table as:-

select top 0* into employee_test1_temp from employee_test1

Step 2: Insert the result of the GROUP BY query into the temporary table as:-

insert into employee_test1_tempselect Emp_ID,Emp_name,Emp_Salfrom employee_test1group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

truncate table employee_test1

Step4: Fill the original table with the rows of the temporary table as:-

insert into employee_test1select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

select * from employee_test1

(b) Without using a temporary table.

with T as( select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank from employee_test1)

deletefrom Twhere rank > 1

nth Highest Salary

2010-03-19T08:06:00.000-07:00

--nth Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

3rd highest salary

2010-03-19T08:05:00.000-07:00

--3rd Highest Salary

Select min(Emp_Sal) from Employee_Test where Emp_Sal in(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

Finding the Highest salary of an employee.

2010-03-19T08:03:00.000-07:00

--Highest Salary
select max(Emp_Sal) from Employee_Test

2009-12-04T10:37:00.000-08:00

1st Day as SQL Web Tutor

My aim to create this blog is to write all such tricks and tips in SQL queries which will be helpful to my friends all over the world.

I want to share my experience about the SQL and hence i am too excited to start with....

Hope you all take me further over the web with my genuine intention to help you out with enjoyment of the technology......