CODEWITHSUNDEEP
sqlsql-server-2005
Suresh Chaudhary
Suresh Chaudhary

Reputation: 1649

get previous row id using stored procedure

My requirement like this:

I have to fetch a row from table "A". Now I have to loop through all the rows and get the values from the ID column. If I get ID = 5 then I want to execute some stored procedure through which I get the previous id row from some table "B". Like 

select * from table b where ID = 4

Buy my question is: if someone deleted that row from the database (e.g. some user deleted the rows with id 3 and 4) - then how can I get the row with id = 2 from the database?

Please give me some sql stored procedure to get previous id record from table.

Upvotes: 1

Views: 1487

Answers (3)

marc_s
marc_s

Reputation: 754868

Hard to understand exactly what it is your looking for - something like this maybe??

CREATE PROCEDURE dbo.FetchPreviousRow @ID INT
AS BEGIN

   SELECT TOP 1 ID, (other columns.....)
   FROM dbo.TableB
   WHERE ID < @ID
   ORDER BY ID DESC

END

This will select the row with the biggest ID less than the @ID you pass in, so:

EXEC dbo.FetchPreviousRow @ID = 5

will return the row with ID = 4 - if it exists. Otherwise, it will return the row with ID = 3 - if it exists. Otherwise the row with ID = 2 - and so on....

Upvotes: 4

Mutation Person
Mutation Person

Reputation: 30520

The following query should get it for you:

select max(ID) from YourTable where ID < @YourID

@YourID is a variable containing your recently inserted ID

Upvotes: 2

Joshua
Joshua

Reputation: 552

How about selecting all ID's less than 5 and sorting them by ID. The first result then would be the one you want.

Upvotes: 1

Related Questions