Solved

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

Posted on 2014-04-02
23
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
23 Comments
 
LVL 13

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 13

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 13

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 13

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 13

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
 
LVL 13

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 13

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 13

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 13

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 13

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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 …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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