Problemas con sp_executesql y Scope_Identity()

Eso ocurre porque SCOPE_IDENTITY devuelve el último IDENTITY insertado
en la tabla en el alcance actual (procedimiento, trigger, función o lote).
Cuando lanzas una ejecución dinámica es como si lanzaras otro lote, por lo
que SCOPE_IDENTITY no te va a retornar lo que tú necesitas.

Si quieres seguir utilizando la ejecución dinámica deberás incluir la
sentencia 'SELECT @newId=SCOPE_IDENTITY' al final, y declarar la variable
@newId como OUTPUT, algo similara a:

Declare @Campo1 int, @newId int
Set @Campo1 = 1

Declare @str nvarchar(500)
Set @str = N'Insert into tabla (campo1) Values (@Campo1)

Exec sp_executesql @str, N'@Campo1 int, @newId int OUTPUT', @Campo1,
@newId=@newId OUTPUT

SELECT @newId [nuevo Identity recién insertado]




Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

Por favor, responder únicamente al foro
Se agradece la inclusión de sentencias DDL


"Mario Barro" escribió en el mensaje
news:
Mostrar la cita

Hola a todos/as;

Estoy realizando una insercción con sql de forma dinámica (sp_executesql)

y
Mostrar la cita

al terminar de ejecutarse necesito recuperar el último valor insertado
mediante Scope_Identity() pero devuelve siempre nulo, aunque la insercción
se realiza.

********************************
Ejemplo:

Declare @Campo1 int
Set @Campo1 = 1

Declare @str nvarchar(500)
Set @str = N'Insert into tabla (campo1) Values (@Campo1)

Exec sp_executesql @str, N'@Campo1 int', @Campo1

Select Scope_Identity()
*******************************
Devuelve Null

¿Por qué ocurre esto?

Saludos

Muchas gracias por tu rápida aclaración.

Estaba pensando en utilizar @@Identity, ya que en principio se realiza una
conexión contra la base de datos, se ejecuta el procedimiento almacenado que
contiene la consulta de insercción dinámica y se desconecta.

Mi único temor es que dichas conexiones son concurrentes y necesito
recuperar con exactitud el último valor generado por cada hilo de ejecución.

Cómo lo ves?

"Carlos Sacristan" escribió en el mensaje
news:
Mostrar la cita

Eso ocurre porque SCOPE_IDENTITY devuelve el último IDENTITY insertado
en la tabla en el alcance actual (procedimiento, trigger, función o lote).
Cuando lanzas una ejecución dinámica es como si lanzaras otro lote, por lo
que SCOPE_IDENTITY no te va a retornar lo que tú necesitas.

Si quieres seguir utilizando la ejecución dinámica deberás incluir la
sentencia 'SELECT @newId=SCOPE_IDENTITY' al final, y declarar la variable
@newId como OUTPUT, algo similara a:

Declare @Campo1 int, @newId int
Set @Campo1 = 1

Declare @str nvarchar(500)
Set @str = N'Insert into tabla (campo1) Values (@Campo1)

Exec sp_executesql @str, N'@Campo1 int, @newId int OUTPUT', @Campo1,
@newId=@newId OUTPUT

SELECT @newId [nuevo Identity recién insertado]




Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

Por favor, responder únicamente al foro
Se agradece la inclusión de sentencias DDL


"Mario Barro" escribió en el mensaje
news:
> Hola a todos/as;
>
> Estoy realizando una insercción con sql de forma dinámica

(sp_executesql)
Mostrar la cita

y
> al terminar de ejecutarse necesito recuperar el último valor insertado
> mediante Scope_Identity() pero devuelve siempre nulo, aunque la

insercción
Mostrar la cita

> se realiza.
>
> ********************************
> Ejemplo:
>
> Declare @Campo1 int
> Set @Campo1 = 1
>
> Declare @str nvarchar(500)
> Set @str = N'Insert into tabla (campo1) Values (@Campo1)
>
> Exec sp_executesql @str, N'@Campo1 int', @Campo1
>
> Select Scope_Identity()
> *******************************
> Devuelve Null
>
> ¿Por qué ocurre esto?
>
> Saludos
>
>

lo mejor sería evitar la ejecución dinámica, así no tendrías problemas, si
no puedes usa la recomendación de carlos, y si no lo consigues, te podemos
general el código completo.


Saludos

Miguel Egea
Microsoft SQL-SERVER MVP
Brigada Anti-Cursores

"Mario Barro" escribió en el mensaje
news:
Mostrar la cita

Muchas gracias por tu rápida aclaración.

Estaba pensando en utilizar @@Identity, ya que en principio se realiza una
conexión contra la base de datos, se ejecuta el procedimiento almacenado

que
Mostrar la cita

contiene la consulta de insercción dinámica y se desconecta.

Mi único temor es que dichas conexiones son concurrentes y necesito
recuperar con exactitud el último valor generado por cada hilo de

ejecución.
Mostrar la cita

Cómo lo ves?

"Carlos Sacristan" escribió en el mensaje
news:
>
> Eso ocurre porque SCOPE_IDENTITY devuelve el último IDENTITY

insertado
Mostrar la cita

> en la tabla en el alcance actual (procedimiento, trigger, función o

lote).
Mostrar la cita

> Cuando lanzas una ejecución dinámica es como si lanzaras otro lote, por

lo
Mostrar la cita

> que SCOPE_IDENTITY no te va a retornar lo que tú necesitas.
>
> Si quieres seguir utilizando la ejecución dinámica deberás incluir

la
Mostrar la cita

> sentencia 'SELECT @newId=SCOPE_IDENTITY' al final, y declarar la

variable
Mostrar la cita

> @newId como OUTPUT, algo similara a:
>
> Declare @Campo1 int, @newId int
> Set @Campo1 = 1
>
> Declare @str nvarchar(500)
> Set @str = N'Insert into tabla (campo1) Values (@Campo1)
>
> Exec sp_executesql @str, N'@Campo1 int, @newId int OUTPUT', @Campo1,
> @newId=@newId OUTPUT
>
> SELECT @newId [nuevo Identity recién insertado]
>
>
>
>
> Un saludo
>
> -
> "Sólo sé que no sé nada. " (Sócrates)
>
> Por favor, responder únicamente al foro
> Se agradece la inclusión de sentencias DDL
>
>
> "Mario Barro" escribió en el mensaje
> news:
> > Hola a todos/as;
> >
> > Estoy realizando una insercción con sql de forma dinámica
(sp_executesql)
> y
> > al terminar de ejecutarse necesito recuperar el último valor insertado
> > mediante Scope_Identity() pero devuelve siempre nulo, aunque la
insercción
> > se realiza.
> >
> > ********************************
> > Ejemplo:
> >
> > Declare @Campo1 int
> > Set @Campo1 = 1
> >
> > Declare @str nvarchar(500)
> > Set @str = N'Insert into tabla (campo1) Values (@Campo1)
> >
> > Exec sp_executesql @str, N'@Campo1 int', @Campo1
> >
> > Select Scope_Identity()
> > *******************************
> > Devuelve Null
> >
> > ¿Por qué ocurre esto?
> >
> > Saludos
> >
> >
>
>

Pues, en primer lugar, intentaría cambiar la forma de insertar los datos
evitando la ejecución dinámica y usando un procedimiento almacenado, y en
segundo lugar, en vez de @@IDENTITY, usaría SCOPE_IDENTITY, porque así
estarás más seguro que el identificador que recoges es el que esperas...

Echa un vistazo a un magnífico artículo de Erland Sommarskog acerca de
las ventajas y desventajas de la ejecución dinámica de sentencias:
http://www.algonet.se/~sommar/dynamic_sql.html


Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

Por favor, responder únicamente al foro
Se agradece la inclusión de sentencias DDL


"Mario Barro" escribió en el mensaje
news:
Mostrar la cita

Muchas gracias por tu rápida aclaración.

Estaba pensando en utilizar @@Identity, ya que en principio se realiza una
conexión contra la base de datos, se ejecuta el procedimiento almacenado

que
Mostrar la cita

contiene la consulta de insercción dinámica y se desconecta.

Mi único temor es que dichas conexiones son concurrentes y necesito
recuperar con exactitud el último valor generado por cada hilo de

ejecución.
Mostrar la cita

Cómo lo ves?

"Carlos Sacristan" escribió en el mensaje
news:
>
> Eso ocurre porque SCOPE_IDENTITY devuelve el último IDENTITY

insertado
Mostrar la cita

> en la tabla en el alcance actual (procedimiento, trigger, función o

lote).
Mostrar la cita

> Cuando lanzas una ejecución dinámica es como si lanzaras otro lote, por

lo
Mostrar la cita

> que SCOPE_IDENTITY no te va a retornar lo que tú necesitas.
>
> Si quieres seguir utilizando la ejecución dinámica deberás incluir

la
Mostrar la cita

> sentencia 'SELECT @newId=SCOPE_IDENTITY' al final, y declarar la

variable
Mostrar la cita

> @newId como OUTPUT, algo similara a:
>
> Declare @Campo1 int, @newId int
> Set @Campo1 = 1
>
> Declare @str nvarchar(500)
> Set @str = N'Insert into tabla (campo1) Values (@Campo1)
>
> Exec sp_executesql @str, N'@Campo1 int, @newId int OUTPUT', @Campo1,
> @newId=@newId OUTPUT
>
> SELECT @newId [nuevo Identity recién insertado]
>
>
>
>
> Un saludo
>
> -
> "Sólo sé que no sé nada. " (Sócrates)
>
> Por favor, responder únicamente al foro
> Se agradece la inclusión de sentencias DDL
>
>
> "Mario Barro" escribió en el mensaje
> news:
> > Hola a todos/as;
> >
> > Estoy realizando una insercción con sql de forma dinámica
(sp_executesql)
> y
> > al terminar de ejecutarse necesito recuperar el último valor insertado
> > mediante Scope_Identity() pero devuelve siempre nulo, aunque la
insercción
> > se realiza.
> >
> > ********************************
> > Ejemplo:
> >
> > Declare @Campo1 int
> > Set @Campo1 = 1
> >
> > Declare @str nvarchar(500)
> > Set @str = N'Insert into tabla (campo1) Values (@Campo1)
> >
> > Exec sp_executesql @str, N'@Campo1 int', @Campo1
> >
> > Select Scope_Identity()
> > *******************************
> > Devuelve Null
> >
> > ¿Por qué ocurre esto?
> >
> > Saludos
> >
> >
>
>

El caso es que el no utilizar ejecucíon dinámica, supondría que si tengo 50
tablas iguales en las cuales sólo cambia el nombre, tendría que crear 50
procedimientos almacenados para realizar las insercciones, cuando con uno
bastaría.
De la otra manera cuando se genera la base de datos, se generan estos
procedimientos almacenados genéricos ( que lo que añaden es que le pasas el
nombre de la tabla) y sirven para todas las tablas que se generen a
posteriori.
No sé si existe otra solución a esta cuestión?

Por otro lado, no acabo de entender el funcionamiento del script que ha
sugerido Carlos y cómo se comportaría ante insercciones por otros procesos
(concurrentemente) de manera masiva.

Otra cuestión (abusando de vuestra paciencia), no se comportaría
correctamente, también recuperar "@@Identity" tras una insercción realizada
en un procedimiento almacenado (con la ejecución dinámica, pero que no
realiza ninguna otra acción más que insertar). Entiendo que cada conexión y
ejecución del procedimiento se realizaría en una sesión independiente, no?

Muchas gracias.