Paging in SQL Server

It is not a good approach to pull of all the data and show in a page.There are two ways to do paging
1- server side (c# or any other language)
2- Database Side
For this article we will implement in SQL Server 2008 R2.We can make a store procedure and pass two parameters
First  is for where we start from.
Second  is  how much you want to show the record.
I just write the main script you can make a store procedure and pass the parameters

DECLARE  @StartRow INT
DECLARE  @EndRow INT

    SET    @StartRow = 1
    SET @EndRow = 30
   
SELECT    RowNumber as 'S/N',FirstName, LastName, EmailAddress
    FROM (
    SELECT   FirstName, LastName, EmailAddress,
                ROW_NUMBER() OVER(
            ORDER BY FirstName, LastName, EmailAddress,Customerid) AS RowNumber
        FROM    customer) customer
    WHERE    RowNumber > @StartRow
        AND RowNumber < @EndRow
    ORDER BY FirstName, LastName, EmailAddress

Create a comma separated list in SQL Query

When we need to get all the value comma separated for specific action. For example I need to get all the values that have status is 1

First thing in mind if we are coding in c# or any other server language we go for loop right ? :) but right now we need to handle in SQL Server
so what first thing will come in mind Cursor right ? :)

There is another way you can get comma separated value without using Cursor see how we can do this

 DECLARE @list_effected_actions VARCHAR(MAX)  
 SELECT @list_effected_actions = COALESCE(@list_effected_actions+',' ,'') + column_name  FROM table where status=1  
 SELECT @list_effected_actions

Get the last inserted id

You can get last inserted id in SQL Server

  DECLARE @Last_Insterted_id integer=0       

 SELECT @Last_Insterted_id=IDENT_CURRENT('tablename')    

Use COMMIT and ROLLBACK Transaction in Store Procedure

 It is a good approach to put query in  try catch block , So if were using insert, update, delete it is a good approach to use commit and rollback as well, so if the query is failed it rollback the transaction



CREATE PROC Sp_NAME
AS    
Begin
    SET NOCOUNT ON;
Begin Try  
 BEGIN TRANSACTION

 --Do your insert update or delete logic whatever you want
    COMMIT TRANSACTION  
End Try  
 Begin Catch  
   ROLLBACK TRANSACTION  
 End catch  
END