Posts

Showing posts from July, 2009

Stored Procedure Pattern in SQL Server 2005

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

SET NOCOUNT ONDECLARE 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 CATCHCLOSE cur_sor DEALLOCATE cur_sor


good luck