CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Kelsey
Kelsey

Reputation: 49

Using RETURN in dynamic SQL to exit stored procedure

I am trying to use something like below in my stored procedure to exit when a certain condition is met, but the code after the return gets executed. Is there something wrong my script?

declare @DBName sysname
set @DBName ='Test_DB'
SET @sql='IF OBJECT_ID('''+@DBName +''',''U'') is null' + CHAR(10)+  'RETURN;' 
EXEC (@sql)

Upvotes: 1

Views: 1300

Answers (2)

Paul Williams
Paul Williams

Reputation: 17040

I tested a similar script:

declare @DBName sysname
set @DBName ='Test_DB'
declare @sql varchar(8000)
SET @sql='IF OBJECT_ID('''+@DBName +''',''U'') is null' +
    CHAR(10) +
    'begin select ''before return'' return end select ''after return'''
EXEC (@sql)

The output of this script on SQL 2014 Developer Edition was 'before return' but not 'after return'. So the return does seem to be working as expected.

Upvotes: 0

Jason W
Jason W

Reputation: 13209

You could use INSERT/EXEC to get the object id of the dynamic object you're checking for, then return if the table has no records not null:

DECLARE @DBName sysname
SET @DBName = 'Test_DB'
SET @sql='SELECT OBJECT_ID('''+@DBName +''',''U'')'
DECLARE @Results TABLE (ObjectId INT)
INSERT @Results
    EXEC (@Sql)
IF NOT EXISTS (SELECT * FROM @Results WHERE ObjectId IS NOT NULL)
    RETURN

Just a side note, OBJECT_ID won't match a database. You'd have to use sys.databases or like that.

Upvotes: 1

Related Questions