tsql - una - asignar valores a variables en sql server

¿Cómo asignar un resultado de selección a una variable?

Question

¿Cómo almaceno un valor de campo seleccionado en una variable de una consulta y lo uso en una declaración de actualización?

Aquí está mi procedimiento:

Estoy escribiendo un procedimiento almacenado SQL Server 2005 T-SQL que hace lo siguiente:

1. Obtiene la lista de id. de facturas de la tabla de facturas y las almacena en Cursor
2. Obtener el ID de factura desde el cursor -> variable tmp_key
3. foreach tmp_key encuentra el ID de contacto principal del cliente de facturas de la tabla de clientes
4. actualiza la clave de contacto del cliente con el ID de contacto principal
5. cerrar el cursor

Aquí está mi código:

DECLARE @tmp_key int
DECLARE @get_invckey cursor 

set @get_invckey = CURSOR FOR 
    select invckey from tarinvoice where confirmtocntctkey is null and tranno like ''%115876''

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey into @tmp_key

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT c.PrimaryCntctKey as PrimaryContactKey
    from tarcustomer c, tarinvoice i
    where i.custkey = c.custkey and i.invckey = @tmp_key

    UPDATE tarinvoice set confirmtocntctkey = PrimaryContactKey where invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

¿Cómo almaceno PrimaryContactKey y lo uso nuevamente en la cláusula set de la siguiente declaración de actualización? ¿Creo una variable de cursor o simplemente otra variable local con un tipo int?

Answer 1

¿Por qué necesitas un cursor? Todo su segmento de código puede ser reemplazado por este, que se ejecutará mucho más rápido en grandes cantidades de filas.

UPDATE tarinvoice set confirmtocntctkey = PrimaryCntctKey 
FROM tarinvoice INNER JOIN tarcustomer ON tarinvoice.custkey = tarcustomer.custkey
WHERE confirmtocntctkey is null and tranno like ''%115876''

Answer 2

Para asignar una variable de forma segura, debe usar la instrucción SET-SELECT:

SET @PrimaryContactKey = (SELECT c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key)

¡Asegúrate de tener un paréntesis inicial y uno final!

La razón por la cual la versión SET-SELECT es la forma más segura de establecer una variable es doble.

1. El SELECT devuelve varias publicaciones

¿Qué sucede si los siguientes resultados de selección en varias publicaciones?

SELECT @PrimaryContactKey = c.PrimaryCntctKey
FROM tarcustomer c, tarinvoice i
WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key

@PrimaryContactKey se le asignará el valor de la última publicación en el resultado.

De hecho, a @PrimaryContactKey se le asignará un valor por publicación en el resultado, por lo que contendrá el valor de la última publicación que procesó el comando SELECT.

La publicación que sea "última" está determinada por cualquier índice agrupado o, si no se usa un índice agrupado o la clave principal está agrupada, la última publicación será la publicación agregada más recientemente. Este comportamiento podría, en el peor de los casos, alterarse cada vez que se cambie la indexación de la tabla.

Con una instrucción SET-SELECT, su variable se establecerá en null .

2. El SELECT no devuelve publicaciones

¿Qué ocurre al utilizar la segunda versión del código si su selección no arroja ningún resultado?

De forma contraria a lo que pueda creer, el valor de la variable no será nulo , ¡retendrá su valor anterior!

Esto se debe a que, como se indicó anteriormente, SQL asignará un valor a la variable una vez por publicación , lo que significa que no hará nada con la variable si el resultado no contiene publicaciones. Por lo tanto, la variable seguirá teniendo el valor que tenía antes de ejecutar la declaración.

Con la instrucción SET-SELECT, el valor será null .

Ver también: ¿ SET versus SELECT cuando se asignan variables?

Answer 3

Prueba esto

SELECT @PrimaryContactKey = c.PrimaryCntctKey
FROM tarcustomer c, tarinvoice i
WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key

UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey 
WHERE invckey = @tmp_key
FETCH NEXT FROM @get_invckey INTO @tmp_key

Usted declararía esta variable fuera de su ciclo como una variable estándar de TSQL.

También debo señalar que así es como lo harías para cualquier tipo de selección en una variable, no solo cuando se trata de cursores.

Answer 4

Solo tuve el mismo problema y ...

declare @userId uniqueidentifier
set @userId = (select top 1 UserId from aspnet_Users)

o incluso más corto:

declare @userId uniqueidentifier
SELECT TOP 1 @userId = UserId FROM aspnet_Users

Answer 5

DECLARE @tmp_key int
DECLARE @get_invckey cursor 

SET @get_invckey = CURSOR FOR 
    SELECT invckey FROM tarinvoice WHERE confirmtocntctkey IS NULL AND tranno LIKE ''%115876''

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey INTO @tmp_key

DECLARE @PrimaryContactKey int --or whatever datatype it is

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT @PrimaryContactKey=c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey AND i.invckey = @tmp_key

    UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey WHERE invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

EDITAR:
Esta pregunta ha obtenido mucha más tracción de la que hubiera anticipado. Tenga en cuenta que no defiendo el uso del cursor en mi respuesta, sino que muestra cómo asignar el valor en función de la pregunta.