Looping through list in SQL

If we have a comma separated list and the requirement is to loop through the list of items one by one or to pass these items to a stored procedure. The easiest and simple way to do this is given below with coments.

--comma separated list
Declare @S varchar(20)
set @S ='abc,def,ghi,jkl' 

--loop through each item
while len(@S) > 0
Begin

--get the first item form the list
declare @S1 varchar(20)
set @S1 = left(@S, charindex(',' , @S+',')-1)

--Print it
Print @S1

--Delete the item from the list
set @S = Stuff(@S,1,charindex(',',@S+','),'')

End

TRANSACTION and Error Handling

Transactions are very useful when you are not sure if there could be a error or not. And if there is a error, the set of sql statements need not be executed or needs to be rolled back.In this case we can TRANSACTIONS that help us to ROLLBACK if there was any error while executing the sql statements.

The below example also shows simple error handling. If there is any error while updating the table then control is passed on to the CATCH block and the TRANSACTION is rolled back.

USE [PUBS]
GO

DECLARE @iError int

BEGIN TRANSACTION

    UPDATE table
    SET column = 'value'
    WHERE column = 'condtion'
   
    select @iError = @@ERROR
    IF (@iError <> 0) GOTO CATCH
   
COMMIT TRANSACTION

CATCH:
IF(@iError <> 0)
BEGIN
    PRINT 'Error message'
    ROLLBACK TRANSACTION
END