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

Comments

Popular posts from this blog

Assembly Microsoft.Dynamics.Service.Plugins.dll can not be loaded. Dynamics CRM 365 Engine version 9 - CRM User creation error

Exception caught instantiating TERADATA report server extension SQL Reporting Services

Could not load file or assembly 'System.ServiceModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. The system cannot find the file specified at Configuration class initiation in CrmServiceHelper.cv