Assigning TOP 1 value to a variable in a stored procedure

Question

Assigning TOP 1 value to a variable in a stored procedure

destino

Say Hey Kid

Points: 677
More actions
November 15, 2015 at 12:28 am

#309684

I am attempting to store the first value returned from a query to a variable in a stored procedure.
The following query performs as expected when run in isolation.
SELECT top 1 MYID
from wheeltravelcard B , (select distinct PartAS,PartWM from partconfig where parttype = 'AS') C
where 1=1
and B.PartNumWM = C.PartWM
andB.CurrentStep in ('000', '010', '020', '030', '040', '050', '070', '080', '090', '100', '110', '120', '130', '140', '150', '160', '170')
and C.PartNUM = 'LZ-0123'
ORDER BY B.CurrentStep DESC,MYID DESC
However when i place it into the stored procedure in the following way
select @currentMYID = TOP 1 MYID
from wheeltravelcard B , (select distinct PartAS,PartWM from partconfig where parttype = 'AS') C
where 1=1
and B.PartNumWM = C.PartWM
andB.CurrentStep in ('000', '010', '020', '030', '040', '050', '070', '080', '090', '100', '110', '120', '130', '140', '150', '160', '170')
and C.PartAS = 'LZ-0123'
ORDER BY B.CurrentStep DESC,MYID DESC
The query presents the following errors when being compiled.
Msg 156, Level 15, State 1, Procedure CR_ScheduleReportProc, Line 52
Incorrect syntax near the keyword 'TOP'.
Msg 102, Level 15, State 1, Procedure CR_ScheduleReportProc, Line 53
Incorrect syntax near 'C'.
Msg 156, Level 15, State 1, Procedure CR_ScheduleReportProc, Line 60
Incorrect syntax near the keyword 'and'.
I'd appreciate it if someone could point out to me the error of my ways.
Thanks
Peter

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182884 More actions · Answer 1

Quick question, can you post the DDL (create table), some sample data as an insert statement and the expected results, without that one can only guess. The subquery in the select statement isn't making too much sense to me, can you elaborate on that a bit further?

😎

Here is a pseudo translation of the code to incorporate the row_number instead of the top/order, still I think some more information is needed to make it work.

DECLARE @currentMYID INT = 0;

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY WC.MYID

ORDER BY WC.CurrentStep DESC

,WC.MYID DESC

) AS WC_RID

,WC.MYID

FROM wheeltravelcard WC

CROSS APPLY

(

SELECT DISTINCT

PartAS

,PartWM

FROM partconfig PC

WHERE PC.parttype = 'AS'

AND PC.PartAS = 'LZ-0123'

) AS PN

WHERE WC.CurrentStep IN ('000', '010', '020', '030', '040'

, '050', '070', '080', '090', '100'

, '110', '120', '130', '140', '150'

, '160', '170')

AND WC.PartNumWM = Pn.PartWM

)

SELECT

@currentMYID = BD.MYID

FROM BASE_DATA BD

WHERE BD.WC_RID = 1;

destino Say Hey Kid Points: 677 More actions · Answer 2

Thank you for responding so quickly.

I can provide what you asked for however the issue is not the fact that the query doesn't give the desired results.

Its the fact that the select using top 1 into a variable doesn't work within a stored procedure

The first select that i provided works.

SELECT top 1 RFID

from wheeltravelcard B , (select distinct PartAS,PartWM from partconfig where parttype = 'AS') C

where 1=1

and B.PartNumWM = C.PartWM

andB.CurrentStep in ('000', '010', '020', '030', '040', '050', '070', '080', '090', '100', '110', '120', '130', '140', '150', '160', '170')

and B.Destination = 'Production'

and B.DNU != 'Scrapped'

and C.PartAS = 'AS-025'

and B.AssignedToOrder = 0

ORDER BY B.CurrentStep DESC,RFID DESC

However when i incorporate it into a stored procedure like this i get an error compiling the procedure.

I have tested other simple queries like

select top 1 * from anytable

and it fails as well.

I'm trying to find a code example that will give me the first result from a query and place it into a variable.

Eirikur Eiriksson SSC Guru Points: 182884 More actions · Answer 3

Can you please elaborate on the purpose of the subquery in the select statement (bold)? Any reason for not joining to the wheeltravelcard table?

😎

SELECT top 1 RFID

from wheeltravelcard B , (select distinct PartAS,PartWM from partconfig where parttype = 'AS') C

where 1=1

and B.PartNumWM = C.PartWM

andB.CurrentStep in ('000', '010', '020', '030', '040', '050', '070', '080', '090', '100', '110', '120', '130', '140', '150', '160', '170')

and B.Destination = 'Production'

and B.DNU != 'Scrapped'

and C.PartAS = 'AS-025'

and B.AssignedToOrder = 0

ORDER BY B.CurrentStep DESC,RFID DESC

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

TOP 1 goes before the variable name, not after. As written, you're saying

@currentMYID = TOP, which is obviously not what you intend.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Phil Parkin SSC Guru Points: 246733 More actions · Answer 5

Try

Select top 1 @Var = Col

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

destino Say Hey Kid Points: 677 More actions · Answer 6

Many thanks for your reply. I suspected that I wasn't far away but i couldn't see what the issue was. In the meantime i built a cursor to do it but i have now removed it and implemented your fix. Again many thanks.

Peter

destino Say Hey Kid Points: 677 More actions · Answer 7

No reason specifically. I started with some pseudo code given to me by the client and it just went from there.

It runs in less than 1 second and shouldn't ever get much longer than that unless the company goes really really gang busters. In which case it might hit 3 seconds.

Given your question i may have a look at a "better" way of writing it.

Cheers and thanks

Peter

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

destino (11/16/2015)
No reason specifically. I started with some pseudo code given to me by the client and it just went from there.
It runs in less than 1 second and shouldn't ever get much longer than that unless the company goes really really gang busters. In which case it might hit 3 seconds.
Given your question i may have a look at a "better" way of writing it.
Cheers and thanks
Peter

Best practice with TSQL/SQL Server is to take a little time to write a query properly - as a beginner you were probably unaware of that. Here's another "better way" to add to the collection already posted:

-- Get the highest value of [RFID] for the highest value of [CurrentStep] in the list:

SELECT TOP 1 @currentMYID = RFID

FROM wheeltravelcard B

CROSS APPLY c

WHERE EXISTS (

SELECT 1

FROM partconfig c

WHERE c.parttype = 'AS'

AND B.PartNumWM = C.PartWM

AND C.PartAS = 'AS-025'

)

ANDB.CurrentStep in ('000', '010', '020', '030', '040', '050', '070', '080', '090', '100', '110', '120', '130', '140', '150', '160', '170')

AND B.Destination = 'Production'

AND B.DNU <> 'Scrapped'

AND B.AssignedToOrder = 0

ORDER BY B.CurrentStep DESC, RFID DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden