How to assign a select result to a variable?

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 1

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