call SP within another SP - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

call SP within another SP

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2014-09-11 : 17:20:58

Hello all,
Can I save the result to my local variable from calling stored procedure ?

i.e
Exec Determine_Class 10, 5 --this sp will return the value of 175

In this SP test, how can i save the result from the calling SP determine_class?
Create procedure test
As
exec Determine_Class 10, 5
go

I tried this, no syntax error but it does't assign the result to @Class . I am still using sql 2000

Create procedure test
As
declare @class varchar(3)
exec @Class = Determine_Class 10, 5
select @Class
go

thank you

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-11 : 19:29:51

When you say "result" do you mean a set of records, like from a SELECT statement or do you mean a return value, like from a RETURN statement? If it is the former then look up INSERT EXEC in Books Online. Beware that you cannot nest these though. If it is the latter, then the stored procedure will only return an integer and your variable is a varchar. Perhaps if you could share a bit about the interface of Determine_Class, we could assist you further.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

nizguy
Starting Member

37 Posts

Posted - 2014-09-12 : 11:16:58

the result is from a select statement. You're right, I tried using the INSERT INTO #TempResult EXEC and it works fine. I just want to find out can i assign the result from a select statement to a variable?
here is my Determine_class stored procedure

Create procedure Determine_Class
(
@Wt int,
@Cf decimal(7,2)
)

as

declare @Density decimal(7,2)
declare @Class int

set @Density = @Wt / @Cf

if @Density >= 35.01
set @Class = 50
else if @Density >= 30.01
set @Class = 55
else if @Density >= 22
set @Class = 60
else
set @Class = 100

Select @Class as Class

go

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-12 : 12:48:41

You need to return the value from Determine_Class using RETURN statement as below:


Create procedure Determine_Class
(
@Wt int,
@Cf decimal(7,2)
)

as

declare @Density decimal(7,2)
declare @Class int

set @Density = @Wt / @Cf

if @Density >= 35.01
set @Class = 50
else if @Density >= 30.01
set @Class = 55
else if @Density >= 22
set @Class = 60
else
set @Class = 100


Select @Class as Class
RETURN @Class
go

Then you can access the value from calling proc as below:

exec @Class = Determine_Class(10, 5)

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources