Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL EXEC and scope

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. :(

like image 651
core Avatar asked Apr 08 '09 21:04

core


People also ask

What is EXEC in SQL query?

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.

What is difference between EXEC and Sp_executesql?

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.

What is SQL and its scope?

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.

How do you pass dynamic parameters in SQL query?

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.


1 Answers

Try

EXEC 'INSERT INTO ' + quotename(@table) ' blah...; SELECT @@IDENTITY'

or better, according to this

EXEC 'INSERT INTO ' + quotename(@table) ' blah...; SELECT SCOPE_IDENTITY()'
like image 115
Jhonny D. Cano -Leftware- Avatar answered Sep 24 '22 06:09

Jhonny D. Cano -Leftware-