assigning result of a stored procedure to a variable

Hi,

I have a stored procedure that needs to call other stored procedures. I would like to assign result (always a single value) of the stored procedure being called to a variable within the main stored procedure. Is it possible? If so, what is the syntax?
By the way
SELECT @var = exec sub_proc param1
Didn't work (wont even compile)

EXEC @var = sub_proc param1
did work, but at run time, instead of assigning stored procedure values to the variable it was simply printing out the sub_proc result.

Thanks!
CREATE PROCEDURE  main_proc
 
AS
BEGIN
.....
DECLARE @var int
SELECT @var = exec sub_proc param1
.....
END

Open in new window

vlad_ozAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashok KumarCommented:
CREATE PROCEDURE  main_proc
 
AS
BEGIN
SET NOCOUNT ON
.....
DECLARE @var int

SELECT @var = sub_proc param1
.....
END


inside sub_proc param1 you should have return value for the variable assigned.
0
Ashok KumarCommented:
replace the line in above code
"SELECT @var = sub_proc param1"
with
Exec @var=sub_proc param1 <params>
0
brejkCommented:
Use return value or OUTPUT parameter in nested procedure or if you need to catch SELECT results from nested procedure use temporary table:

A) return value

USE tempdb
GO

CREATE PROC procA
AS
RETURN 1
GO

CREATE PROC procB
AS
DECLARE @var int
EXEC @var = procA
SELECT @var
GO

EXEC procB

B) OUTPUT parameter

USE tempdb
GO

CREATE PROC procA
@Output int OUTPUT
AS
SET @Output = 1
GO

CREATE PROC procB
AS
DECLARE @var int
EXEC procA @var OUTPUT
SELECT @var
GO

EXEC procB

C) temp table

USE tempdb
GO

CREATE PROC procA
AS
SELECT 1 AS A
GO

CREATE PROC procB
AS
CREATE TABLE #temp (col1)
INSERT INTO @var (col1)
EXEC procA
SELECT * FROM #temp
DROP TABLE #temp
GO

EXEC procB
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

vlad_ozAuthor Commented:
Thanks for the quick response guys!

 SOTGarmentsIndiaPrivateLimited,
1)
"SELECT @var = sub_proc param1" doesn't even compile

2)
"Exec @var=sub_proc param1 <params>" compiles, but when I run it I get something like:
-----------
7
(1 row(s) affected)
-----------
0
(1 row(s) affected)

Where 7 is the correct value, but its not being and 0 is printout of the @var variable, so it looks like values isn't actually being assigned to the variable.

0
vlad_ozAuthor Commented:
Hi brejk,

The temp table approach seemed to work.
Thanks!
0
brejkCommented:
Also remember that in SQL Server 2005 instead of using # table you can use table variable which seems a better solution to me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.