Tutorial :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?


Solution: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 has gotten a lot more traction than I would have anticipated. Do note that I'm not advocating the use of the cursor in my answer, but rather showing how to assign the value based on the question.


Solution:2

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  


Solution:3

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.


Solution:4

Why do you need a cursor at all? Your entire segment of code can be replaced by this, which will run a lot faster on large numbers of rows.

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


Solution:5

In order to assign a variable safely you have 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 a starting and an ending parenthesis!

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

1. The SELECT returns several posts

What happens if the following select results in several posts?

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 in the result.

In fact @PrimaryContactKey will be assigned one value per post in the result, so it will consequently contain the value of the last post the SELECT-command was processing.

Which post is "last" is determined by any clustered indexes or, if no clustered index is used or the primary key is clustered, the "last" post will be the most recently added post. This behavior could, in a worst case scenario, be altered every time the indexing of the table is changed.

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

2. The SELECT returns no posts

What happens, when using the second version of the code, if your select does not return a result at all?

In a contrary to what you may believe the value of the variable will not be null - it will retain it's previous value!

This is because, as stated above, SQL will assign a value to the variable once per post - meaning it won't do anything with the variable if the result contains no posts. So, the variable will still have the value it had before you ran the statement.

With the SET-SELECT statement the value will be null.

See also: SET versus SELECT when assigning variables?


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »