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    gets list of invoices id s from invoice table and stores to Cursor  Fetch invoice id from cursor -  tmp key variable  foreach tmp key finds invoice client primary contact id from customer table updates the client contact key with primary contact id  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

User · Accepted Answer

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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

[tsql] How to assign a select result to a variable?

Examples related to tsql

Examples related to cursor