Re: ask for standard sp writing

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 16:16:47 +0530

Other than sampthas suggestions, try to avoid using cursors.
most of the times there is a set based equivalent. 

-- 
Roji. P. Thomas
SQL Server Programmer
"Mullin Yu" <mullin_yu@ctil.com> wrote in message
news:umSVG5ACEHA.1236@TK2MSFTNGP11.phx.gbl...
> hi,
>
> i want to know the standard sp writing if i have several select sql and
> update sql statement.
>
> how can write it with transaction and error handling? the following is
mine
>
> - i have only one begin tran
> - i have the error handling module
> - i put return value 0 as successful and 1 as failure
>
> i want to follow a better template and approach.
>
> thanks a lot!
>
> CREATE      PROC ResetJob as
>     BEGIN
>
>  SET NOCOUNT ON
>
>  Begin Tran
>
>   -- *************************************************************
>  -- select JobID which is hanging
>         -- Using Cursor
>  -- *************************************************************
>
>  -- Declare the variables to store the values returned by FETCH.
>  DECLARE @tmpJobID as bigint
>
>  -- Declare the variable to store the concat value
>  DECLARE @tmpString as varchar(200)
>
>  -- Initialize
>  SET @tmpString = ''
>
>  DECLARE SampleCrsr CURSOR FOR
>
>  -- search for any hanging Job, whose status = 2 (working)
>  -- and the LastWorkingDate >= WorkerTimeout
>  select JobID from OutboundQueue, OutboundConfig where Status = 2 and
> datediff(minute, LastWorkingDate, getDate()) > WorkerTimeout
>
>
>  -- Create temp Table for storing JobItemIDs to be processed
>  CREATE TABLE #tmpJobID(tJobID bigint)
>
>  OPEN SampleCrsr
>
>  -- Perform the first fetch and store the values in variables.
>  -- Note: The variables are in the same order as the columns
>  -- in the SELECT statement.
>
>  FETCH NEXT FROM SampleCrsr INTO @tmpJobID
>
>  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
>  WHILE @@FETCH_STATUS = 0
>  BEGIN
>
>     --SET @tmpString = @tmpString + Convert(varchar, @tmpJobItemID) + ', '
>     INSERT INTO #tmpJobID (tJobID) Values (@tmpJobID)
>
>     IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>     -- Concatenate and display the current values in the variables.
>     --PRINT 'JobItemID: ' + Convert(varchar, @tmpJobItemID)
>
>     -- This is executed as long as the previous fetch succeeds.
>     FETCH NEXT FROM SampleCrsr INTO @tmpJobID
>  END
>
>  --PRINT 'tmpString: ' + @tmpString + '1'
>
>  CLOSE SampleCrsr
>  DEALLOCATE SampleCrsr
>
>  -- ***************************************************************
>  -- End Of using cursor
>  -- ***************************************************************
>
>
>  -- Update
>  -- Table OutboundQueue: Status, LastWorkingDate, No_of_Failure
>  -- Table OutboundQueueItem: Status, No_of_Failure, LastWorkingDate,
> IsLocked, MachineLocked
>  Update OutboundQueue set Status = 1, LastWorkingDate = getDate(),
> NoOfFailure=NoOfFailure+1 where JobID in (select tJobID from #tmpJobID)
>  IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>  Update OutboundQueueItem set Status = 1, LastWorkingDate = getDate(),
> NoOfFailure=NoOfFailure+1, IsLocked = 0, MachineLocked = null where JobID
in
> (select tJobID from #tmpJobID)
>  IF (@@ERROR <> 0) GOTO ERR_HANDLER
>
>  Commit Tran
>
>  RETURN 0
>
>  ERR_HANDLER:
>  PRINT 'Unexpected error occurred!'
>  ROLLBACK TRAN
>  RETURN 1
>
>
>  End
>
> GO
>
>

Relevant Pages