[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

assigning result of a stored procedure to a variable

Posted on 2008-02-11
6
Medium Priority
?
4,544 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:vlad_oz
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 20865551
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
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 20865558
replace the line in above code
"SELECT @var = sub_proc param1"
with
Exec @var=sub_proc param1 <params>
0
 
LVL 18

Accepted Solution

by:
brejk earned 1500 total points
ID: 20865593
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

Author Comment

by:vlad_oz
ID: 20865796
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
 

Author Closing Comment

by:vlad_oz
ID: 31429752
Hi brejk,

The temp table approach seemed to work.
Thanks!
0
 
LVL 18

Expert Comment

by:brejk
ID: 20865893
Also remember that in SQL Server 2005 instead of using # table you can use table variable which seems a better solution to me.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question