how to exit a stored procedure if a condition passes

user_8630565 0
Viewed 18735

I have to stop my stored procedure in the middle when a if condition satisfies. i used NOEXEC ON it shows all the above results till NOEXEC ON STATEMENT. But i need only the if statement result without the above results is it possible.

DECLARE @var1  VARCHAR(MAX),
 @var2  VARCHAR(MAX),
 @var3  VARCHAR(MAX)

 SET @var1 = 'ASH'
 SET @var3 = 'ASHff'
 print @var3

IF @var1 <> ''
    BEGIN
    PRINT 'Information available'
    SET NOEXEC ON
    END 

    SET @var2 = 'DFGF'

    SET NOEXEC OFF

when i run it i got this result:

ASHff
Information available

but expected output is :

Information available

is it possible?

sql-server-2012 tsql sql
2 Answers

Based on your comment, I believe this is the order of steps you would want in the SP. You can stop execution on SP anytime you want by using RETURN. Also I would not use NOEXEC, as based on your requirement we don't need to.

DECLARE @var1  VARCHAR(MAX),
 @var2  VARCHAR(MAX),
 @var3  VARCHAR(MAX)

 SET @var1 = 'ASH'
 SET @var3 = 'ASHff'

IF @var1 <> ''
    BEGIN
    PRINT 'Information available'
    return;
    END 

 print @var3

    SET @var2 = 'DFGF'


----------
user_24502240

Also, other way to come out of a SP when a certain condition is met is to use GOTO

DECLARE @var1  VARCHAR(MAX),
@var2  VARCHAR(MAX),
@var3  VARCHAR(MAX)

SET @var1 = 'ASH'
SET @var3 = 'ASHff'

IF @var1 <> ''
 BEGIN
 PRINT 'Information available'
 GOTO xxxx
 END 

print @var3

xxxx:

SET @var2 = 'DFGF'
print @var2

You can also place xxxx right before the final END

user_139576530
Related