Retun status from execution of stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retun status from execution of stored procedure

Author

Topic

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 14:31:27

Hi,

I have a situation, where I am trying to get return status after executing a stored procedure which has parameters, one paramters is quoted

I am getting below data after fetching a cursor values
PROC_NAME=dbo.load_dim
PROC_PARM='EUR.dbo', DIM_D, FACT_T

set @SQL=@PROC_NAME+' '+@PROC_PARM

Now I am trying to execute this procedure with parameters but not able to get return status
EXEC @ret=@SQL

Here error is
"Could not locate entry in sysdatabases for database 'dbo'. No entry found with that name. Make sure that the name is entered correctly."

I tried below
EXEC @ret = sp_executesql @SQL
This executes , but doesn't return actual return status. Always returns 0

Looking for your help

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 14:50:16

before the "EXEC @ret=@SQL" put this in to see what you are actually EXECing:
print @sql

Do your SPs have an RETURN statements? ie: "return 12"
If not the return status will always be 0 assuming no errors are raised.

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 14:54:33

Thanks for the reply.

My SP has return statement & when I execute that SP directly I am getting error .So same I am expecting from above code.

Printing @sql shows it is passing correct string to EXEC statement
dbo.load_dim 'EUR.dbo', DIM_D, FACT_T

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 15:08:14

If these are parameters then don't they need to be quoted:
dim_d, fact_t

ie:
exec dbo.load_dim 'EUR.dbo', 'DIM_D', 'FACT_T'

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 15:15:39

No. Inside that procedure, dbo.load_dim they are being taken care of

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 15:29:28

Well, if they are input parameters to your procedure and the are any of (char,varchar,nchar,nvarchar,text,ntext) then they need to be quoted to pass them in.

This will generate a syntax error before the SP code is even executed:
dbo.load_dim 'EUR.dbo', DIM_D, FACT_T

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 15:33:54

Hi TG,

They are not generating any syntax error,
When I execute that procedure individually it is getting succeeded without any error

execute dbo.load_dim 'EUR.dbo', DIM_D, FACT_T
Command(s) completed successfully.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 15:45:51

Oh, I guess sql does the conversion implicitly. Ok then what's the problem? No return status?

I think you are getting the RETURN status of sp_executesql rather than from the procedure you are calling via sp_executesql. So you are using sp_executesql to execute a dynamically built call to another SP? Sounds Klugy

I think you need to capture the return status of the called SP into an OUTPUT variable of the sp_executesql call.

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 15:51:39

Right.. when using sp_executesql, i am getting error status of it rather than of procedure.

Answer to your question is Yes. I am trying sp_executesql alternatively (as EXEC is not working) to execute procedure

Can you please tell me how to get the return status of the called SP into an OUTPUT variable of the sp_executesql call?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 15:51:59

This is what I mean. It is ugly but it works :)


create proc junk 
@a varchar(10),
@b varchar(10),
@c varchar(10)

as

select @a,@b,@c
return 12
go

declare @sql nvarchar(50)
       ,@param nvarchar(50)
       ,@ret int
select @sql = N'exec @rt = dbo.junk ''EUR.dbo'', DIM_D, FACT_T'
       ,@param = '@rt int output'

exec sp_executesql @sql, @param, @ret output
select @ret [@ret]
go


drop proc junk

output:
---------- ---------- ----------
EUR.dbo    DIM_D      FACT_T

(1 row(s) affected)

@ret
-----------
12

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 16:25:09

Thanks TG.

I am trying to execute your given solution

Only doubt is how to replace varibale for below string
select @sql = N'exec @rt = dbo.junk ''EUR.dbo'', DIM_D, FACT_T'
,@param = '@rt int output'

Here I have this dbo.junk ''EUR.dbo'', DIM_D, FACT_T in variable @SQL. Also there are only one quote around EUR.dbo

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 16:49:52

>>Also there are only one quote around EUR.dbo
As you probably know, to add a literal single quote in a quoted string in T-SQL you need to precede the literal single quote with another (escape character) single quote.

How are you building these values? Are you using T-Sql code or some external application code? If it is t-sql go ahead and post what you've got if you're having trouble...

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 17:04:59

DECLARE @PROC_NAME varchar(500), @PROC_PARM varchar(300)

DECLARE C_list_cursor CURSOR FOR
select SQL_STATE,SQL_PARAM
from ENG.dbo.eur_table

OPEN C_list_cursor
FETCH NEXT FROM C_list_cursor INTO @PROC_NAME,@PROC_PARM

-- Now I have below values after fetching from cursor
-- PROC_NAME=dbo.load_dim
-- PROC_PARM='EUR.dbo', DIM_D, FACT_T

set @STRG=@PROC_NAME+' '+@PROC_PARM

-- I just want to execute STRG & get output status
-- below is your code
declare @sql nvarchar(200),@param nvarchar(200, @ret int
select @sql = N'exec @rt = dbo.junk ''EUR.dbo'', DIM_D, FACT_T'
,@param = '@rt int output'

exec sp_executesql @sql, @param, @ret output
select @ret [@ret]

-- Now how to replace above in red with @STRG. Also note single quotes around 'EUR.dbo'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-17 : 18:10:56

Part of you problem may be the quotes around your SP. Here are two variations (of the values in [eur_table]) that will work:



USE TEMPDB

-------------------------------------------------------
set nocount on
go
--set up my test objects
create proc dbo.load_dim 
@a varchar(20)
,@b varchar(20)
as 
select @a [param1], @b [param2]
return 12
go

create table eur_table (SQL_STATE varchar(20), SQL_PARAM varchar(20))
go

insert eur_table values ('dbo.load_dim', '''DIM_D'', ''FACT_T''')
insert eur_table values ('dbo.load_dim', 'DIM_D, FACT_T')

select * from eur_table
go


-------------------------------------------------------
declare @param nvarchar(200)
       ,@ret int
       ,@strg nvarchar(50)


DECLARE @PROC_NAME varchar(500), @PROC_PARM varchar(300)

DECLARE C_list_cursor CURSOR FOR 
select SQL_STATE,SQL_PARAM
from dbo.eur_table

OPEN C_list_cursor
FETCH NEXT FROM C_list_cursor INTO @PROC_NAME,@PROC_PARM
while @@fetch_status = 0
begin

       set @STRG=N'exec @rt = ' + @PROC_NAME+' '+@PROC_PARM
       set @param = '@rt int output'

       SELECT @strg [@strg]

       exec sp_executesql   @stmt = @strg
                            ,@param = @param
                            ,@rt = @ret output

       select @ret [@ret]

       print ''
       print ''
       print 'NEXT RUN'
       
       FETCH NEXT FROM C_list_cursor INTO @PROC_NAME,@PROC_PARM
end
close C_list_cursor
deallocate C_list_cursor
go


drop proc dbo.load_dim 
drop table eur_table 


OUTPUT:
SQL_STATE            SQL_PARAM
-------------------- --------------------
dbo.load_dim         'DIM_D', 'FACT_T'
dbo.load_dim         DIM_D, FACT_T

@strg
--------------------------------------------------
exec @rt = dbo.load_dim 'DIM_D', 'FACT_T'

param1               param2
-------------------- --------------------
DIM_D                FACT_T

@ret
-----------
12

 
 
NEXT RUN
@strg
--------------------------------------------------
exec @rt = dbo.load_dim DIM_D, FACT_T

param1               param2
-------------------- --------------------
DIM_D                FACT_T

@ret
-----------
12

EDITi:
if you are stuck with your table values of PROC_NAME being quoted then you can remove them from the call by using REPLACE:

set @STRG=N'exec @rt = ' + replace(@PROC_NAME,'''','') +' '+@PROC_PARM

Be One with the Optimizer
TG

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-17 : 18:46:50

TG, you are a genious. You solved my problem. Thanks

Actually quotes was not a problem. Getting return status from procedure instead of sp_executesql was key issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources