Question: How do I store a selected field value into a variable from a query and use it in an update statement?

Here is my procedure:

I'm writing a SQL Server 2005 T-SQL stored procedure which does the following:

1. gets list of invoices id's from invoice table and stores to Cursor
2. Fetch invoice id from cursor -> tmp\_key variable
3. foreach tmp\_key finds invoice client primary contact id from customer table
4. updates the client contact key with primary contact id
5. 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 do I store the PrimaryContactKey and use it again in the set clause of the following update statement? Do I create a cursor variable or just another local variable with an int type?

Answer: 
I just 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

```
Try This

```
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 declare this variable outside of your loop as just a standard TSQL variable.

I should also note that this is how you would do it for any type of select into a variable, not just when dealing with cursors.