Stored Procedure Pattern in SQL Server 2005

Here is a basic stored procedure concept i personally offer to advanced SQL Server users;

SET NOCOUNT ON

DECLARE cur_sor CURSOR

DECLARE @CURSOR_FETCH_STATUS INT

DECLARE @varible1 as [anytype] ,@variable2 as [anytype],....

SET cur_sor = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR

SELECT .... FROM .....

/*

for Arguments Detail check http://msdn.microsoft.com/en-us/library/ms180169.aspx

*/

OPEN cur_sor

BEGIN TRY
BEGIN TRANSACTION

FETCH NEXT FROM cur_sor INTO @varible1,@variable2 ,...

/* not more than selected columns above*/

SELECT @CURSOR_FETCH_STATUS = @@FETCH_STATUS

WHILE @CURSOR_FETCH_STATUS = 0

BEGIN

....... /* INSERT,DELETE,UPDATE ( DML) or any other process */ .......

FETCH NEXT FROM cur_sor INTO @varible1,@variable2 ,...

SELECT @CURSOR_FETCH_STATUS = @@FETCH_STATUS

END

COMMIT

END TRY

BEGIN CATCH

PRINT(ERROR_NUMBER())

PRINT(ERROR_SEVERITY())

PRINT(ERROR_STATE())

PRINT(ERROR_PROCEDURE())

PRINT(ERROR_LINE())

PRINT(ERROR_MESSAGE())

ROLLBACK

END CATCH

CLOSE cur_sor

DEALLOCATE cur_sor

good luck