Getting XML from SQL

--------------------------------------------------------------------
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

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

Begin Trans t
// Do processing

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

Error Handling- Use GOTO

-------------------
//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

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

@@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

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 , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Data Transfer and table creation

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

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

Table Data Script Generator

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

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

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

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