CODEWITHSUNDEEP
sql-server-2005stored-procedures
mcamara
mcamara

Reputation: 753

Generic Select Stored Procedure

I want to create a stored procedure based following way:

ALTER PROCEDURE spGetLastId
(
@table_name varchar(100)
)
AS
BEGIN
    DECLARE @DBSql varchar(2000)
    SET @DBSql = 'SELECT MAX(id) as ''LastId'' FROM ' + @table_name
    EXEC sp_executesql @DBSql
END

This procedure will have to display the last id of any table that I pass as parameter.

Thanks and Regards,

Upvotes: 3

Views: 631

Answers (3)

TabbyCool
TabbyCool

Reputation: 2839

Not sure if it's what you're looking for, but if you're inserting records then retrieving the last identity value generated then you can use SELECT SCOPE_IDENTITY() after your insert...

INSERT INTO MyTable (col1, col2) 
VALUES (val1, val2);
SELECT SCOPE_IDENTITY()

Otherwise your procedure should work for what you want, although you should change the data type of @DbSql to nvarchar rather than varchar.

Upvotes: 1

Raj More
Raj More

Reputation: 48016

The only use case I know for doing this kind of work is using building your own IDENTITY fields.

If you are doing that, please stop and use the IDENTITY provided by SQL Server.

If you want to insert your own values into the IDENTITY column read up on SET IDENTITY_INSERT TABLENAME ON;

Upvotes: 0

Alex K.
Alex K.

Reputation: 175776

Change @DBSql from varchar to nvarchar and that will work, alternatively look at the built-in IDENT_CURRENT().

Upvotes: 3

Related Questions