Return value from Stored procedure to an other SP 1

[saturnius]

Hi, I want to call a Stored Procedure from an other SP.

SP1 is as follows:
*********************************
CREATE PROCEDURE DetailUpdate
(
@strname char (30),
@id int,
@rv varchar output
)
AS
exec @rv = getstatus @strname,@id
select (@rv)
GO
************************************
CREATE PROCEDURE getstatus
(
@strname char (30),
@id int
)
as
Declare @sql varchar (1000)
select @sql = ('select status from '+@strname+' where id ='+convert(varchar,@id))
exec (@sql)
GO
************************************
This is working in the Query analyzer
***********************************
declare @strname char (30)
declare @id int
declare @SQL VarChar(1000)

set @strname = 'demanddetail'
set @id = 29

select @SQL =('select status from '+@strname+' where id ='+convert(varchar,@id))
exec (@SQL)
**************************************
I just get 'NULL' as a result (not '0') with the SP - the result of the query analyzer is '2'. How do I return the value to the other SP?
This is bothering me now for quite a while and I would be really happy if someone could help me out. Many thanks in advanec!

Just from the Northwind DB -
*********************************
declare @strCompany2Table char (30)
declare @employeeid int
Declare @SQL VarChar(1000)

set @strCompany2Table = 'employees'
set @employeeid= 7

select @SQL =('select extension from '+@strCompany2Table+' where employeeid ='+convert(varchar,@employeeid))
exec (@SQL)
*********************************

 

[sunila7]

Hi,

Try this.... Change ur SP getstatus like this and use sp_executesql to return value from a dynamic SQL.

CREATE PROCEDURE getstatus
(
@strname char (30),
@id int,
@status int output
)
as
Declare @sql varchar (1000)
select @sql = ('select status from '+@strname+' where id ='+convert(varchar,@id))

EXECUTE sp_executesql @SQL, N'@Ststus INT OUTPUT', @Status OUTPUT

GO

Hope it helps

Sunil

 

[cheyney]

Sprocs return 3 types of things: output parameters, return values (kindof a special type of output parameter), and result sets.

When you use this syntax:

exec @rv = getstatus @strname,@id

you are reading the return value of the sproc getstatus into the variable @rv. the return value of a sproc can only be an integer, and is expicitly set in the sproc with the RETURN statement. Any values "returned" with a select statement, as you do in getstatus, are returned in the result set. I don't believe a sproc can return BOTH a result set AND return/output values at the same time.

What you can do to make this work is to alter your getstatus sproc to return your value as a return value. Because you are using dynamic sql, you need to create a temp table and insert into it, then select from it.

Heres the code

CREATE PROCEDURE getstatus
(
@strname char (30),
@id int
)
as
Declare @sql varchar (1000)
DECLARE @RETURNVALUE int

select @sql = ('select status from '+@strname+' where id ='+convert(varchar,@id))

CREATE TABLE #temp (tempnumber int)

INSERT INTO #temp
exec (@sql)

SELECT @RETURNVALUE = tempnumber
FROM #temp

DROP TABLE #temp

RETURN @RETURNVALUE
GO


hope this helps to clarify some things...

Cheyney

 

[cheyney]

(damn! reply posted while i was writing mine)

Or just use sunil's method

Cheyney

 

[saturnius]

Hello,
Thanks a lot both of you. It works very well - actually I dropped the second SP and it is now only one.
Have a nice day! Cheers!
Saturnius