asked on

how to supress the returned data row from a stored procedure

Hi:

I am calling a stored procedure, which returns an int, let's call it sp1, from another stored procedure, sp2. I need to store the returned value from sp1 in a variable but I don't want that returned value to be part of the returned data row of sp2. Any way I can pull this off?

Currently the following gives me two returned data set and I do not want the first one:

-- in sp2:
...
DECLARE @myvariable int
EXEC @myvariable= spMyStoredProcedure 'arg1', 'arg2' --sp1, it returns one int
select @myvariable
...

Thanks.

Surendra Nath

if you need it to be part of the return variable then you should be put it down in the return statement at the end of the stored procedure ( remember if you put the return in the middle,the next statements wont be executed).

Check this out

create procedure sp_test1
(
 @a VARCHAR(50),@b VARCHAR(50)
)
as
BEGIN
declare @t INT 
set @t = 5 -- This can be set to any value or programatically set down the stream.
-- do something or some selects, updates, deletes 
return @t
END


Create procedure sp_test2
as
BEGIN
-- do something or nothing
DECLARE @myvariable int
exec @myvariable = dbo.sp_test1 'a','B' 
select @myVariable.
END

Open in new window

sdc248

ASKER

Looks like my problem is that the return value from sp1 (sp_test1 in your example) was actually from a SELECT statement instead of a RETURN statement. That is, if I change the last statement in sp_test1 to be "select @t", I'd get '5' as a data row followed by a '0' as a 2nd data row from sp_test2.

Anything I can do in sp_test2 to resolve the issue without changing sp_test1?

Surendra Nath

so, what you want the end result to be...

You want to read the 5 and leave the zero??? a bit confused here.

sdc248

ASKER

Assuming the last statement in sp_test1 was "select @t", I want sp_test2 to return 5 and only 5.

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdc248

ASKER

The 2nd scenario works for me. Thanks.