How to assign a select result to a variable?
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
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?
Solution 5
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'
Related videos on Youtube
phill
Programming server admin stuff with vbscript, python, powershell, php, and c#.
Updated on July 05, 2022Comments
-
phill about 2 years
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?
-
John over 9 yearsAs @GilaMonster answers below, this entire operation can be a single UPDATE statement (a "set-based operation", not to be confused with a t-sql SET statement) which is a much better approach (faster execution, less overhead, and significantly less code). I am just pointing this out because the question and all current top answers are about how to write a SET statement, but it's really not the best approach to begin with.
-
phill about 15 yearsAre cursors really frowned upon?
-
GilaMonster about 15 yearsThey're slow. SQL Server is optimised for set-based queries. It's faster for it to operate on a million rows in one query than to operate on one row a million times. Add to that the overhead that cursors have, and you're asking for major performance problems by using cursors rather than set-based operations Test your cursor solution and my query, see what the execution times of the two are.
-
CallMeLaNN about 13 yearsHaha, I like this. should be very simple for assigning scalar value. Hate cursors bla3.. Luckly googling found this little answer.
-
CallMeLaNN about 13 yearsI don't know because
set @userId = (select top 1 UserId from aspnet_Users)
without bracket will lead to "incorrect syntax near select"! -
Chip McCormick over 10 yearsThis forum shows the proper approach for top: sqlservercentral.com/Forums/Topic496124-169-1.aspx
-
Chip McCormick over 10 yearsNote that the use of the cursor above is not related to the question of how to assign to a variable. Unfortunately that makes this a confusing question.
-
wexman over 10 yearsErk is correct and this should have been marked as answer. The second point is what caught me recently...
-
Erk about 9 yearsI just solved a bug with code following case 1 above. The original coder seemed not to care that several rows were selected, but unfortunately a MS SQL Server may sometimes return the rows in another order than they were inserted for no other apparent reason than optimization... the result was random and confusing...
-
Alan Ball about 9 yearsThanks man!!!! This did it for me, also easier than working out how to change the cursor for my specific scenario. You are a champion!
-
Erk over 7 yearsBeware of using TOP, since SQL server may rearrange rows to do performance tuning when being stressed with many queries. The first line may not always be the same.