Example for converting Cursor to While loop

Just a small example on how to replace a cursor using an while loop.

Table t1 has one column id of type int.

Assume that a cursor is defined for the query:

Select id from t1

This example narrates how a cursor defined for the above mentioned query
can be replaced using While loop and table variable.

DECLARE  @tbl_var  TABLE(
                         tid     INT,
                         row_num INT    IDENTITY ( 1 , 1 )
                         )

DECLARE  @rowcnt INT

DECLARE  @val INT

DECLARE  @row_move INT

INSERT INTO @tbl_var
           (tid)
SELECT id
FROM   t1

SET @rowcnt = @@ROWCOUNT

SET @row_move = 1

WHILE @rowcnt >= @row_move
  BEGIN
    SELECT @val = tid
    FROM   @tbl_var
    WHERE  row_num = @row_move
    
    EXEC Sample_sp_call
      @val
    
    SET @row_move = @row_move + 1
  END

Do's and Dont's while writing Sps

Recently at my workplace I was asked to provide a briefing on Dos and Donts while writing stored procedures. I have just posted a modified version of it.

> Avoiding sub queries/Converting sub queries to Correlated sub queries
I have discussed the same at length in my previous post which can be found here.

> Replace cursors using while loops

Cursors involve row by row processing which can slowdown the execution. Cursor can effectively replaced by while loop and a temporary table to provide better performance. This can also help one avoid errors that occur due to locking.

Example for the same can be found here

> Usage of order by/ distinct

Order by/distinct clause internally results in a sort operation which can increase the execution time. So, Order by/Distinct should be used minimally and only when it is inevitable.

> Effective usage of temp table / table variable.

Long queries when broken down by storing the temporary results to temporary tables/ table variables provide considerable performance boost. Usage of table variables / temporary tables allows one to reuse the previously obtained results and also reduce the duration of locks held on objects. But, Usage of temp table/ temp variable does cause a load on tempdb and system memory.Getting into the details of choice of selection for table variable/temp table is beyond the scope of this post. Please note that if one has a long transaction/query temp table and table variables are good options to look at.

> Avoiding select *

Column names should be specified instead of specifying ‘*’. This ensures unwanted data is not selected. Using ‘*’ can cause syntax errors when the source or destination table structure is modified.

> Avoid non - Sargable conditions

Using non Sargable conditions on ‘where’ clause causes table scan and hence the index created on the column does not get used. Non Sargable conditions are a list of conditions on usage puts the index in vain. They are 'OR', 'not in', <>, column as a parameter of function., eg: dateadd(dd,1,date_of_birth).


> Following ANSI style join

Outer Joins on tables should follow the ANSI Style join as Microsoft supports only the ANSI style joins from SQL Server 2005.

> SET NOCOUNT ON

SET NOCOUNT ON should be added at the starting of all stored procedures as it reduces the additional overhead of calculating row count on the compiler.

> Transaction handling

Transactions should be kept as short as possible as it increases duration of locks held on the objects. Transaction opened using begin transaction should always commits or rollbacks on all paths of the execution flow.
Nested transactions should always be avoided.

> Error Handling

Error handling should be been done at appropriate places using @@Error variable.

The above listed points were obtained from the most common mistakes committed by the developers.There are many many books which speak about effective T SQL Programming. The above list is just obvious ones that I have come across.