How to assign the result of selecting a variable?

Question

How to save the selected field value in a variable from the query and use it in the update statement?

Here is my procedure:

I am writing a SQL Server 2005 T-SQL stored procedure that does the following:

• retrieves a list of invoice identifiers from the invoice table and stores them in Cursor
• Get account ID from cursor → tmp_key variable
• foreach tmp_key finds the primary contact identifier of a customer invoice from a customer table
• updates client contact key with primary contact identifier
• close cursor

Here is my code:

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 

How to save PrimaryContactKey and use it again in the set clause of the next update statement? Create a cursor variable or another local variable with a type of type?

Answer 1

 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 

EDIT:
This question got a lot more traction than I expected. Please note that I do not recommend using the cursor in my answer, but rather indicate how to assign a value based on the question.

Answer 2

I had the same problem and ...

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

or even shorter:

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

Answer 3

try it

 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 

You would specify this variable outside your loop as a standard TSQL variable.

I should also note that this way you can do this for any type of selection in a variable, and not just when working with cursors.

Answer 4

Why do you need a cursor? You can replace your entire code segment with this, which will work much faster on a large number of lines.

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

Answer 5

To assign a variable safely, you need to use the SET-SELECT statement:

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

Make sure you have both start and end brackets!

The reason why the SET-SELECT version is the safest way to set a variable is twofold.

1. SELECT returns multiple messages

What happens if the following selection results in multiple messages?

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

@PrimaryContactKey will be assigned the value from the last post as a result.

In fact, @PrimaryContactKey will be assigned one value per message as a result, so it will contain the value of the last message that the SELECT command processed.

Which message is the "last" is determined by any clustered indexes, or if the cluster index is not used or the primary key is not clustered, the last message will be the last message added. In the worst case, this behavior can be changed each time the table is indexed.

With the SET-SELECT statement, your variable will be set to null .

2. SELECT does not return messages

What happens if you use the second version of the code, if your choice does not return a result at all?

In contrast to what you can believe, the value of the variable will not be null - it will retain the same value!

This is due to the fact that, as indicated above, SQL will assign a value to a variable once per message - this means that nothing will happen to the variable if the result does not contain messages. Thus, the variable will still have the value that it had before the statement was run.

Using the SET-SELECT statement, the value will be null .

See also: SET or SELECT when assigning variables.