Solved

SQL use output value from a stored prod within a stored proc

Posted on 2014-04-02
23
203 Views
Last Modified: 2014-04-10
I'd like to use the following to place the stored proc aspGetDistributionid output into my local variable @iDistributionid
At the moment I get an error
Must declare the scalar variable "@iDistributionid".

although if I just execute up to the word go, I get a value for iDistributionOUTid in the results pane.  Confused


declare @iDistributionid int

 execute aspGetDistributionid 1,-1, @iDistributionOUTid = @iDistributionid OUTPUT

go

aspRptRenewalsExpiriesByIssue 38,123,'01 apr 2014',-1,@iDistributionid
0
Comment
Question by:louise_8
  • 13
  • 10
23 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
In the stored procedure aspGetDistributionid you need to set the @iDistributionOUTid parameter as output. When you then call the stored procedure, the variable you use as input parameter for the stored procedure which corresponds with the @iDistributionOUTid will be filled with the number you want.
For example, if I have a very simple procedure:

create procedure uSP_sum_of_2_variables

	@value1		numeric(10,2)
,	@value2		numeric(10,2)
,	@sum		numeric(10,2) output

as

Select	@sum = @value1 + @value2

go

Open in new window


Notice the output after the @sum parameter declaration.
Now, when you call the procedure, add the word output to the 3rd parameter as well:

declare @sum numeric(10,2)

exec uSP_sum_of_2_variables		2
							,	3
							,	@sum output
							
select @sum

Open in new window


As you can see, the variable @sum is now filled with the output value of the stored procedure.
0
 

Author Comment

by:louise_8
Comment Utility
Thanks for your comments, I seem to be following that but still no luck

if I run
declare @iDistributionOUTid int
exec aspGetDistributionid 123 ,-1, @iDistributionOUTid output
the results window says
iDistributionOUTid 170
[if I add afterwards (becuase really I want to use the variable to send to another procedure)]
select @iDistributionOUTid

I get results window 1
iDistributionOUTid 170

 results window 2
[no column name]  null

I cant figure out why?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Can you post the code for the procedure aspGetDistributionid you're calling? It sounds like your procedure is already resulting in a select statement which will display a result, rather than populating the output variable.
0
 

Author Comment

by:louise_8
Comment Utility
here you go:

ALTER procedure [dbo].[aspGetDistributionid]  @iProductGroupId int,
@iIssuesAgo int,
@iDistributionOUTid int =0 output
AS  

 
set nocount on
begin


DECLARE @SQLString nvarchar(2000)


DECLARE @nIssues int
SET @nIssues = abs(@iIssuesAgo)
/* Build the SQL string one time. */
SET @SQLString =   'SELECT TOP 1 iDistributionOUTid = iDistributionid FROM (  SELECT TOP '
SET @SQLString = @SQLString +   + convert(varchar(10),@nIssues)
                     + char(13)
                   
SET @SQLString = @SQLString + ' iDistributionid FROM tbDistribution where tbDistribution.iProductGroupId = '

SET @SQLString = @SQLString  + convert(varchar(10),@iProductGroupid)  + char(13)
SET @SQLString = @SQLString  + ' ORDER BY iDistributionid desc) d ORDER BY iDistributionid '

exec sp_executesql @SQLString
 
   
 return

end
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Ok, that confirms my suspicion. You're not actually using the output variable.
You will have to change your select statement in the @SQLstring so that it selects the result as the @iDistributionOUTid variable. So something along the lines of:

SET @SQLString =   'SELECT @iDistributionOUTid = (SELECT TOP 1 iDistributionOUTid = iDistributionid FROM (  SELECT TOP ' 
SET @SQLString = @SQLString +   + convert(varchar(10),@nIssues)
                     + char(13)
                    
SET @SQLString = @SQLString + ' iDistributionid FROM tbDistribution where tbDistribution.iProductGroupId = '

SET @SQLString = @SQLString  + convert(varchar(10),@iProductGroupid)  + char(13) 
SET @SQLString = @SQLString  + ' ORDER BY iDistributionid desc) d ORDER BY iDistributionid) '

Open in new window


Not 100% sure I got the changes to the dynamic SQL correct, but I think it should run like this. I think you can remove the "return" at the end of the statement.
0
 

Author Comment

by:louise_8
Comment Utility
THANKS

running the sp alone now, I get the error
Must declare the scalar variable "@iDistributionOUTid

any ideas?
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Does the same happen when you change the line which executes the @SQLString to

EXECUTE (@SQLString)

Open in new window

0
 

Author Comment

by:louise_8
Comment Utility
yes :(
0
 

Author Comment

by:louise_8
Comment Utility
changing
SELECT @iDistributionOUTid = (SELECT TOP

to

SELECT iDistributionOUTid = (SELECT TOP

gets rid of the error but again when I run

declare @iDistributionOUTid int
exec aspGetDistributionid 1 ,-1, @iDistributionOUTid output
select @iDistributionOUTid

I get I get results window 1
iDistributionOUTid 170

 results window 2
[no column name]  null
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Ok, that's not nice of SQL, it doesn't recognize the variable outside of the dynamic SQL. Let's try this:

ALTER procedure [dbo].[aspGetDistributionid]  @iProductGroupId int,
@iIssuesAgo int,
@iDistributionOUTid int =0 output
AS  

 
set nocount on
begin


DECLARE @SQLString nvarchar(2000)


DECLARE @nIssues int
SET @nIssues = abs(@iIssuesAgo)
/* Build the SQL string one time. */
SET @SQLString = 'Create table #DistID (iDistributionOUTid int)'
SET @SQLString = @SQLString +   ' INSERT INTO #DistID SELECT TOP 1 iDistributionOUTid = iDistributionid FROM (  SELECT TOP ' 
SET @SQLString = @SQLString +   + convert(varchar(10),@nIssues)
                     + char(13)
                    
SET @SQLString = @SQLString + ' iDistributionid FROM tbDistribution where tbDistribution.iProductGroupId = '

SET @SQLString = @SQLString  + convert(varchar(10),@iProductGroupid)  + char(13) 
SET @SQLString = @SQLString  + ' ORDER BY iDistributionid desc) d ORDER BY iDistributionid '

exec sp_executesql @SQLString

Select @iDistributionOUTid = (select iDistribitionOUTid from #DistID)

Drop Table #DistID

End

Open in new window


The Dynamic SQL creates a temp table, inserts the value into that table, which is then read into the output variable before dropping the temp table.
Off to lunch, let me know how that goes.
0
 

Author Comment

by:louise_8
Comment Utility
thanks again

new error:
Msg 208, Level 16, State 0, Procedure aspGetDistributionid2, Line 29
Invalid object name '#DistID'.

(1 row(s) affected)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Try ##DistID instead. Replace all occurances.
0
 

Author Comment

by:louise_8
Comment Utility
same, sorry

Loggin off for a few days, I'll reply then

Thanks so much for your help so far
0
 

Author Comment

by:louise_8
Comment Utility
actual error is Msg 208, Level 16, State 0, Procedure aspGetDistributionid2, Line 29
Invalid object name '#DistID'.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Use this:

ALTER procedure [dbo].[aspGetDistributionid]  @iProductGroupId int,
@iIssuesAgo int,
@iDistributionOUTid int =0 output
AS  

 
set nocount on
begin


DECLARE @SQLString nvarchar(2000)

Create table ##DistID (iDistributionOUTid int)

DECLARE @nIssues int
SET @nIssues = abs(@iIssuesAgo)
/* Build the SQL string one time. */
SET @SQLString = 'INSERT INTO ##DistID SELECT TOP 1 iDistributionOUTid = iDistributionid FROM (  SELECT TOP ' 
SET @SQLString = @SQLString +   + convert(varchar(10),@nIssues)
                     + char(13)
                    
SET @SQLString = @SQLString + ' iDistributionid FROM tbDistribution where tbDistribution.iProductGroupId = '

SET @SQLString = @SQLString  + convert(varchar(10),@iProductGroupid)  + char(13) 
SET @SQLString = @SQLString  + ' ORDER BY iDistributionid desc) d ORDER BY iDistributionid '

exec sp_executesql @SQLString

Select @iDistributionOUTid = (select iDistribitionOUTid from #DistID)

Drop Table ##DistID

End

Open in new window


I think the error was thrown just because the temp table was created within the dynamic sql, might still have worked. But I also realized there's no reason to put it in the @SQLString variable. The query above creates the temp table first, then executes the dynamic SQL which inserts the value. Finally it reads the value you want in the output variable. Would be surprised if it still gives an error.
0
 

Author Comment

by:louise_8
Comment Utility
Thanks again.  Error now is
Msg 208, Level 16, State 0, Procedure aspGetDistributionid2, Line 29
Invalid object name '#DistID'.

changing to #Distid gives me the error
Msg 207, Level 16, State 1, Procedure aspGetDistributionid2, Line 29
Invalid column name 'iDistribitionOUTid'.
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 150 total points
Comment Utility
I'm getting sloppy. I missed one reference to the table when I added a #.

ALTER procedure [dbo].[aspGetDistributionid]  @iProductGroupId int,
@iIssuesAgo int,
@iDistributionOUTid int =0 output
AS  

 
set nocount on
begin


DECLARE @SQLString nvarchar(2000)

Create table ##DistID (iDistributionOUTid int)

DECLARE @nIssues int
SET @nIssues = abs(@iIssuesAgo)
/* Build the SQL string one time. */
SET @SQLString = 'INSERT INTO ##DistID SELECT TOP 1 iDistributionOUTid = iDistributionid FROM (  SELECT TOP ' 
SET @SQLString = @SQLString +   + convert(varchar(10),@nIssues)
                     + char(13)
                    
SET @SQLString = @SQLString + ' iDistributionid FROM tbDistribution where tbDistribution.iProductGroupId = '

SET @SQLString = @SQLString  + convert(varchar(10),@iProductGroupid)  + char(13) 
SET @SQLString = @SQLString  + ' ORDER BY iDistributionid desc) d ORDER BY iDistributionid '

exec sp_executesql @SQLString

Select @iDistributionOUTid = (select iDistribitionOUTid from ##DistID)

Drop Table ##DistID

End

Open in new window

0
 

Author Comment

by:louise_8
Comment Utility
thanks for all your help.
Same error again though
Msg 207, Level 16, State 1, Procedure aspGetDistributionid2, Line 29
Invalid column name 'iDistribitionOUTid'.
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 150 total points
Comment Utility
As Homer Simpson would say: "DOH!!"
It's a typo....
Where it says "select @distributionOUTid = (select iDistribitionOUTid from ##DistID)
It doesn't say distriBUTion, but distriBITion.

Change the I to a U and you should be good to go:

Select @iDistributionOUTid = (select iDistributionOUTid from ##DistID)

Open in new window

+

Sorry about that.
0
 

Author Comment

by:louise_8
Comment Utility
ha ha I cannot believe I missed that either.

Good news, it works now yippeee

Thanks for all you're help, much appreciated!!
0
 

Author Closing Comment

by:louise_8
Comment Utility
Thank you for your patience!!!
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
You're welcome. Glad it's finally working. Just remember to assign 500 points to your next questions ;).
0
 

Author Comment

by:louise_8
Comment Utility
:)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now