SQL Stored Procedure Question (Experts Onry Plz) 3

[monksnake]

Is there a way to immediately stop execution of a stored procedure that has an output parameter anytime certain criteria is met?

My situation is this (cheap SP skeleton) :

if errorcheck begin
set @outputString = 'error'
--if I hit this error, I don't need to execute anymore of the SP
end

if errorcheck2 begin
set @outputString = 'other error'
end


Basically, anytime I hit an error I'd like to stop execution of the SP and return the @outputString.

I'm thinking I can do this with multiple return statements, but I'm not sure.


<.

 

[sunila7]

I think using GOTO statement and RETURN statement should help in consolidating all the exits from SP in 1 location.



Sunil

 

[gmmastros]

Something like this...

Alter Procedure MonkSnake
	@Input Int,
	@Output VarChar(100) out
As
If @Input = 1
  Begin
    Set @Output = 'I don''t Like 1.'
    Return
  End

If @Input = 2
  Begin
    Set @Output = '2 isn''t much better.'
    Return
  End

Set @Output = 'That'' more like it.'

Test like this....

Declare @OutputVar VarChar(100)

Exec MonkSnake [!]1[/!], @OutputVar out

Select @OutputVar

Change the value of the number to see the different responses.

-George

"the screen with the little boxes in the window." - Moron

 

[SQLDenis]

example

create proc blatest @errorcheck int,@outputString varchar(50) output
as

if @errorcheck  =0
begin
  set @outputString = 'error'
  --if I hit this error, I don't need to execute anymore of the SP
end

if @errorcheck  =1
 begin 
  set @outputString = 'other error'
end

declare @v varchar(50)
exec blatest 0, @v output
select @v
go

declare @v varchar(50)
exec blatest 1, @v output
select @v

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Are you talking about something like this?

CREATE PROCEDURE dbo.ProcName_DEL
    -- list input parameters first, then optional parameters, and finally all output parameters:
    @intPKid         int               -- REQUIRED primary key for record to be processed
    , @intProcessIND int = NULL OUTPUT -- OPTIONAL parameter for returning managed exception value 
    , @bitDebug      bit = 0           -- OPTIONAL development flag to toggle PRINT statements on/off
AS
/*
Procedure Name:  ProcName_DEL
Description:  TEMPLATE

Copyright XYZ 2007, all rights reserved.

Project v01.00.01


Revision History
Name              Date        Reason     Description
L. N. Bruno       01/01/1900  Original   Initial version
*/

-- eliminate SQL Server's "done in proc" messages:
SET NOCOUNT ON

-- standard debugging/error routine variables:
DECLARE
    @vcProcedureName varchar(200)
    , @vcStage       varchar(200)
    , @vcStagePlus   varchar(200)
    , @intError      int

-- procedure specific variables:
DECLARE
    @vcMessage varchar(2000)

/*
-- FOR TESTING:
declare 
    @intPKid         int
    , @intProcessIND int
    , @bitDebug      bit
    
set @intPKid       = 1
set @intProcessIND = NULL
set @bitDebug      = 1    -- execute the print statements for debugging

-- END TESTING
*/

-- set default values for variables:
SET @vcProcedureName = 'ProcName_DEL'
SET @vcStage         = NULL
SET @vcStagePlus     = NULL
SET @intError        = 0

BEGIN
    -- initialize debugging:
    IF @bitDebug = 1 PRINT '================================================================================'
    IF @bitDebug = 1 PRINT 'Procedure ' + @vcProcedureName + ' started at ' + RTRIM(CONVERT(varchar, GETDATE(), 109)) + '.'

        SET @vcStage = 'Step 1:  validate primary key and perform "soft" delete'
        SET @vcStagePlus = space(4) + 'Primary key: ' + RTRIM(CONVERT(varchar(5), @intPKid))
        IF @bitDebug = 1 PRINT ' '
        IF @bitDebug = 1 PRINT @vcStage
        IF @bitDebug = 1 PRINT @vcStagePlus
        
        IF EXISTS(SELECT PrimaryKey FROM dbo.TableName WHERE PrimaryKey = @intPKid)
            BEGIN
                BEGIN TRAN @vcProcedureName
                    SET @vcStagePlus = space(4) + 'Primary key found; performing soft delete'
                    IF @bitDebug = 1 PRINT @vcStagePlus

                    UPDATE dbo.TableName
                        SET SoftDeleteIND = 1
                        , Updated_User    = ''
                        , Updated_Date    = GETDATE()
                    WHERE PrimaryKey = @intPKid

                    SET @intError = @@ERROR
                    IF @intError > 0
                        BEGIN
                            ROLLBACK TRAN
                            SET @vcStagePlus = space(4) + 'Error in performing soft delete'
                            IF @bitDebug = 1 PRINT @vcStagePlus
                            GOTO ErrExit
                        END

                    IF @@ROWCOUNT > 1
                        BEGIN
                            ROLLBACK TRAN
                            SET @vcStagePlus = space(4) + 'More than one record would be affected; transaction was rolled back.'
                            IF @bitDebug = 1 PRINT @vcStagePlus
                            GOTO ErrExit
                        END

                COMMIT TRAN @vcProcedureName
            END
        ELSE
            BEGIN
                SET @vcStagePlus = space(4) + 'Primary key not found in table.'
                IF @bitDebug = 1 PRINT @vcStagePlus
                SET @intProcessIND = -1 -- not found
                RETURN
            END

    -- conclude debugging:
    IF @bitDebug = 1 PRINT ' '
    IF @bitDebug = 1 PRINT 'Procedure ' + @vcProcedureName + ' successfully ended at ' + RTRIM(CONVERT(varchar, GETDATE(), 109)) + '.'
    IF @bitDebug = 1 PRINT '================================================================================'

    -- reset SQL Server's "done in proc" messages:
    SET NOCOUNT OFF

END

-- ERROR MANAGEMENT
ErrExit:
IF @intError = 0 SET @intError = @@ERROR
SET @vcMessage = 'Procedure ' + @vcProcedureName + ' failed at ' + RTRIM(CONVERT(varchar, GETDATE(), 109)) + '.'
SET @vcMessage = @vcMessage + @vcStage + @vcStagePlus
SET @vcMessage = @vcMessage + '; Error code:  ' + RTRIM(CONVERT(varchar(10), @intError))
IF @intError > 0 RAISERROR(@vcMessage, 16, 1)
-- END ProcName_DEL
GO

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[SQLDenis]

LNBruno

>>-- ERROR MANAGEMENT
>>ErrExit:
>>IF @intError = 0 SET @intError = @@ERROR

@@ERROR will always be 0 in this case

you are already checking here

SET @intError = @@ERROR
IF @intError > 0

by doing this

SET @vcStagePlus = space(4) + 'Error in performing soft delete'
or this

SET @vcStagePlus = space(4) + 'More than one record would be affected; transaction was rolled back.'


@@ERROR has been reset to 0



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Oops! You're right. I knew there was a reason I stopped using that template... ;-)

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[SQLDenis]

here is an example

declare @interror int

select 1/0
select @interror =@@error

select 0
select @@error,@interror

@@error will be zero in the last select

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[monksnake]

Thanks all, that's what I was looking for.

<.

 

[monksnake]

The main answer I was going for is the answer to "Can I put just 'return' anywhere many times in my SP and will it still keep the output variable value.

George answered it perfectly.

<.

 

[LNBruno]

Okay. Show of stars: how many of us missed George last week (Alex doesn't get to vote since he snagged TMotW in his absence)?

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[onpnt]

Sorry I'm not an expert, I can spell Only ;-)

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ESquared]

I had to wonder if monksnake was asking us to be ornery experts.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]