Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT TOP into variable Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2008 1:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 28, 2010 4:42 PM
Points: 47, Visits: 68
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
Post #496124
Posted Wednesday, May 7, 2008 2:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 40,197, Visits: 36,600
The top's in the wrong place

SELECT TOP 1 @ID = OrderVal FROM qryCust 
WHERE OrderNo = '000663' AND DiscountGroup = 'XXND' AND CustID = 3




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #496128
Posted Wednesday, May 7, 2008 3:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 28, 2010 4:42 PM
Points: 47, Visits: 68
DOH! Many thanks!

Post #496157
Posted Wednesday, May 7, 2008 1:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 23, 2008 1:43 PM
Points: 89, Visits: 206
Hello,

The TOP can go to the right of the assignment too.

like

select @variable = ( select TOP 1 salesAmount from sales )

Regards,

Terry
Post #496631
Posted Wednesday, May 7, 2008 1:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 40,197, Visits: 36,600
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #496639
Posted Wednesday, April 20, 2011 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 7:08 AM
Points: 3, Visits: 77
Thanks :)
Post #1096567
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse