• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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

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
louise_8
Asked:
louise_8
  • 13
  • 10
2 Solutions
 
Koen Van WielinkIT ConsultantCommented:
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
 
louise_8Author Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
louise_8Author Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
louise_8Author Commented:
THANKS

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

any ideas?
0
 
Koen Van WielinkIT ConsultantCommented:
Does the same happen when you change the line which executes the @SQLString to

EXECUTE (@SQLString)

Open in new window

0
 
louise_8Author Commented:
yes :(
0
 
louise_8Author Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
louise_8Author Commented:
thanks again

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

(1 row(s) affected)
0
 
Koen Van WielinkIT ConsultantCommented:
Try ##DistID instead. Replace all occurances.
0
 
louise_8Author Commented:
same, sorry

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

Thanks so much for your help so far
0
 
louise_8Author Commented:
actual error is Msg 208, Level 16, State 0, Procedure aspGetDistributionid2, Line 29
Invalid object name '#DistID'.
0
 
Koen Van WielinkIT ConsultantCommented:
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
 
louise_8Author Commented:
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
 
Koen Van WielinkIT ConsultantCommented:
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
 
louise_8Author Commented:
thanks for all your help.
Same error again though
Msg 207, Level 16, State 1, Procedure aspGetDistributionid2, Line 29
Invalid column name 'iDistribitionOUTid'.
0
 
Koen Van WielinkIT ConsultantCommented:
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
 
louise_8Author Commented:
ha ha I cannot believe I missed that either.

Good news, it works now yippeee

Thanks for all you're help, much appreciated!!
0
 
louise_8Author Commented:
Thank you for your patience!!!
0
 
Koen Van WielinkIT ConsultantCommented:
You're welcome. Glad it's finally working. Just remember to assign 500 points to your next questions ;).
0
 
louise_8Author Commented:
:)
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 13
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now