Geek asks and answers

General selection of stored procedure

I want to create a stored procedure as follows:

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 identifier of any table that I pass as a parameter.

Thanks and respect,

+3
source share
3 answers

Change @DBSqlfrom varcharto nvarcharand it will work, alternatively look at the built-in IDENT_CURRENT().

+3
source

Not sure if this is what you are looking for, but if you insert records and then retrieve the last generated identification value, you can use SELECT SCOPE_IDENTITY () after your insert ...

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

, , @DbSql nvarchar, varchar.

+1

The only use case I know for doing this kind of work is to create my own IDENTITY fields.

If you do this, stop and use the IDENTITY provided by SQL Server.

If you want to insert your own values ​​in the IDENTITY column, read SET IDENTITY_INSERT TABLENAME ON ;

0
source

More articles:


All Articles