Let's say I have a stored procedure with this in its body:
EXEC 'INSERT INTO ' + quotename(@table) ' blah...'
SELECT IDENT_CURRENT('' + @table + '')
Is IDENT_CURRENT() guaranteed to get the identity of that row INSERTed in the EXEC? IDENT_CURRENT() "returns the last identity value generated for a specific table in any session and any scope", but the scope is different within the EXEC than the stored procedure, right?
I want to make sure that if the stored procedure is being called multiple times at once, the correct identity is SELECTed.
EDIT: Or do I need to do both the INSERT and SELECT within the EXEC, like so:
declare @insert nvarchar
set @insert =
'INSERT INTO ' + quotename(@table) ' blah...' +
'SELECT IDENT_CURRENT(''' + @table + ''')'
EXEC @insert
And if that's the case, how do I SELECT the result of the EXEC if I want to continue with more code in T-SQL? Like this (although it's obviously not correct):
declare @insert nvarchar
set @insert =
'INSERT INTO ' + quotename(@table) ' blah...' +
'SELECT IDENT_CURRENT(''' + @table + ''')'
declare @ident int
set @ident = EXEC @insert
-- more code
SELECT * FROM blah
UPDATE: In the very first snippet, if I SELECT SCOPE_IDENTITY() instead of using IDENT_CURRENT(), NULL is returned by the SELECT. :(
The EXEC command is used to execute a stored procedure, or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC.
sp_executesql supports parameterisation, whereas EXEC only accepts a string. Only performance differences that may arise are due to the parameterisation i.e. a parameterised sp_executesql call is more likely to have a reusable cached plan.
The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.
Dynamic SQL queries are those built at runtime based on one or more variable values. To execute those queries, we must concatenate them into one SQL statement and pass them as a parameter to the sp_executesql stored procedure.
Try
EXEC 'INSERT INTO ' + quotename(@table) ' blah...; SELECT @@IDENTITY'
or better, according to this
EXEC 'INSERT INTO ' + quotename(@table) ' blah...; SELECT SCOPE_IDENTITY()'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With