Search this blog

Tuesday, June 2, 2009

Get the last inserted record from SQL Table without identity or DateTime

In this stored procedure, we are using the cursor concept to fetch last record.

This Procedure accepts Tablename as input, and it returns last record from that table

create Procedure Get_LastRow

(

-- Table name to retrieve last record

@Tname Varchar(50)

)

AS

BEGIN

-- Cursor Declaration

-- By Default Cursor is Forward Only Cursor, by that we can't directly get the Last Record,

-- If we want to get the last record directly, we should go for dynamic cursor

EXECUTE ('DECLARE GETLAST CURSOR DYNAMIC FOR SELECT * FROM ' + @Tname)

OPEN GETLAST

FETCH LAST FROM GETLAST

CLOSE GETLAST

DEALLOCATE GETLAST

END

To Execute this Procedure, follow the sample given below.

in this sample, we passed the tablename 'dbo.TableA' as parameter, it will return last record from the table if data exists, else return blank rows

EXEC Get_LastRow 'dbo.TableA'

1 comment:

  1. That's great, however in an instance where the Primary Key is a varchar, SQL Server orders the table alphabetically, which means this stored procedure always returns the last alphabetical entry in the table.

    Is it possible to select the last row chronologically when this is the case?

    ReplyDelete