SELECT TOP into variable
-
Sorry if this is a really simple question, but how do I select the top 1 into a variable? Something like this (but not this as it doesn't work!):
SELECT @ID = TOP 1 OrderVal FROM qryCust WHERE OrderNo = '000663' AND DiscountGroup = 'XXND' AND CustID = 3
I'd just lie to add that I am building this up as a sQL string as I have a variable mnumber and combination of parameters to include in the where clause.
So my@ID is the output parameter from my sp_executesql.
Many thanks
-
The top's in the wrong place
SELECT TOP 1 @ID = OrderVal FROM qryCustWHERE OrderNo = '000663' AND DiscountGroup = 'XXND' AND CustID = 3Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
DOH! Many thanks!
-
Hello,
The TOP can go to the right of the assignment too.
like
select @variable = ( select TOP 1 salesAmount from sales )
Regards,
Terry
-
In this case yes. That's assigning the results of a subquery to a variable. and only works with a single variable and column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply