Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using output parameters

Status
Not open for further replies.

nelco

Programmer
Apr 4, 2006
93
US
Hi,
I am using output parameters in one of the stored procedures and am calling this stored proc in another stored proc.

But I am getting this error:
Msg 164, Level 15, State 1, Line 10
Each GROUP BY expression must contain at least one column that is not an outer reference.
Msg 164, Level 15, State 1, Line 15
Each GROUP BY expression must contain at least one column that is not an outer reference.

How can I use output parameters to get the output?

Code:
DECLARE 
@FirstCcy varchar(7),
@SecondCcy varchar(7),
@Volume1 NUMERIC(18,2),
@Volume2 NUMERIC(18,2)
EXEC [pTest_FTDM_Details] '07-17-2008', '07-23-2008', @FirstCcy OUTPUT, @SecondCcy OUTPUT, @Volume1 OUTPUT, @Volume2 OUTPUT


--SUM UP the volume for each currency(FirstCcy)
Select	@Volume1 AS CcyVolume, @FirstCcy AS Ccy
Into	#A
GROUP BY @FirstCcy

--SUM UP the volume for each currency(SecondCcy)
Insert Into #A
Select	@Volume2, @SecondCcy
GROUP BY @SecondCcy

---Output the data 

--SUM UP the volume for each currency
Select	Ccy AS Currency, SUM(CcyVolume) AS CurrencyVolume
From #B
Group By Ccy

 
The Error is not due to Output parameter, error shows that it is with the query....

--SUM UP the volume for each currency(FirstCcy)
Select @Volume1 AS CcyVolume, @FirstCcy AS Ccy
Into #A
GROUP BY @FirstCcy

Group by works on table, you are not selecting anything from any table, so you have syntax issue here.

First create your temp table #A and then insert the data into that table without using Group by.

Thanks

 
Thanks. My mistake.
I did the following. It shows the result as NULL when I use the output parameters

Code:
DECLARE 
@FirstCcy varchar(7),
@SecondCcy varchar(7),
@Volume1 NUMERIC(18,2),
@Volume2 NUMERIC(18,2)
EXEC [pTest_FTDM_Details] '07-17-2008', '07-23-2008', @FirstCcy OUTPUT, @SecondCcy OUTPUT, @Volume1 OUTPUT, @Volume2 OUTPUT

Select @FirstCcy, @SecondCcy, @Volume1, @Volume2

OUTPUT:
NULL	NULL	NULL	NULL
 
Well where do you initialize the values for the variables?
Please post the whole proc code.

"NOTHING is more important in a database than integrity." ESquared
 
I made an error. I was trying to capture multiple values to a variable. I will output the data into a table rather.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top