Solved

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

Posted on 2014-04-02
23
206 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
ID: 39973999
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
ID: 39974045
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
ID: 39974058
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
ID: 39974068
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
ID: 39974073
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
ID: 39974107
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
ID: 39974120
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
ID: 39974126
yes :(
0
 

Author Comment

by:louise_8
ID: 39974133
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
ID: 39974138
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
ID: 39974169
thanks again

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

(1 row(s) affected)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39974181
Try ##DistID instead. Replace all occurances.
0
 

Author Comment

by:louise_8
ID: 39974292
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
ID: 39974299
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
ID: 39974306
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
ID: 39977187
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
ID: 39977206
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
ID: 39990373
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
ID: 39990492
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
ID: 39990871
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
ID: 39990873
Thank you for your patience!!!
0
 
LVL 12

Expert Comment

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

Author Comment

by:louise_8
ID: 39990928
:)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

911 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

23 Experts available now in Live!

Get 1:1 Help Now