Using returned variables in a SQL Server query

Question

I am using SQL Server 2012 and I have a query which returns two columns, the Item number and the Row number of that item in the query:

Row    Item  
--------------
 1     1234  
 2     5632  
 3     4213  
 4     0912

Before I run the query I will know that I am only interested in the row containing Item 5632 and X number of rows following the one containing item 5632 (in this case lets just say rows 2 and 3). However, I will not know ahead of time what row Item 5632 is in.

I would like to somehow say

Where Row >= Row of Item 5632  
  And Row <= Row of Item 5632 + X

Is this possible to do? Thank you all!

Please update this thread, either accepting an answer or providing detail via comment or edit or even self answer why it doesnt answer your issue. — Daniel Brose
– Daniel Brose, Commented May 18, 2020 at 2:50

Adam C · Accepted Answer · 2016-05-12 01:15:24Z

1

Supposing the query you have now is SELECT RowNo, Item FROM Tbl, the following query can replace it and do what you want:

DECLARE @Item = 5632
DECLARE @ItemRowNo = SELECT RowNo FROM Tbl WHERE Item = @Item
DECLARE @Qty = 2

SELECT RowNo, Item 
FROM Tbl
WHERE Item >= @ItemRowNo
AND Item < (@ItemRowNo + @Qty)
ORDER BY RowNo

If you give me your actual current query, I can update this answer to reflect it.

You may choose to declare less things than what I did if they will be constant, but I'm guessing that you will in fact be SELECTing them from elsewhere.

edited May 12, 2016 at 1:15

answered May 12, 2016 at 0:10

Adam C

1031 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:31:41Z

0

More than one way to do this, im more accustomed to nested queries

Nested select statement in SQL Server

SQL nested query in SQL server

More specifically:

SELECT TOP 3 a.row, a.item FROM tableA a 
WHERE a.row >= (SELECT row FROM tableA WHERE item = 5632) 
ORDER BY a.row

TOP doesnt worry about actual value of ROW, just limits number of retrieved records

http://www.w3schools.com/sql/sql_top.asp

http://www.w3schools.com/sql/sql_where.asp

http://www.w3schools.com/sql/sql_orderby.asp

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered May 11, 2016 at 23:52

Daniel Brose

1,40411 silver badges24 bronze badges

3 Comments

FuzzyTree Over a year ago

missing order by statement

Vamsi Prabhala Over a year ago

there is no limit in sql server

Daniel Brose Over a year ago

Noticed that as soon as I posted initial answer, i usually try to add links and proof it after seeing how it reads, updating now, thanks for the spot

Collectives™ on Stack Overflow

Using returned variables in a SQL Server query

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related