SQL Server - storing SELECT statement into a variable in a Stored Procedure

Question

In my Stored Procedure, I am trying to store the result of a SELECT statement inside a variable. This is what I have so far:

DECLARE @process_pk uniqueidentifier
INSERT INTO @process_pk
    SELECT process_pk 
    FROM dbo.Process 
    WHERE process_id = @process_id

In the above, I'm trying to store the result of the SELECT statement into my variable @process_pk, to be used sometime later in the latter part of my stored procedure. But it doesn't seem to be the correct syntax.

I would like to ask what is the correct way to store SELECT statement results inside a Stored Procedure variable so that it can be used anytime within the stored procedure.

SqlKindaGuy · Accepted Answer · 2018-06-29 06:05:22Z

6

You should use SET

SET @process_PK = (SELECT process_pk 
    FROM dbo.Process 
    WHERE process_id = @process_id)

answered Jun 29, 2018 at 6:05

SqlKindaGuy

3,6172 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-06-29 11:13:34Z

1

You can set the value in the SELECT:

SELECT @process_pk = process_pk 
FROM dbo.Process 
WHERE process_id = @process_id;

This is particularly handy if you want to set multiple values from the same query:

SELECT @process_pk = p.process_pk,
       @col1 = p.col1 
FROM dbo.Process p 
WHERE p.process_id = @process_id;

answered Jun 29, 2018 at 11:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Server - storing SELECT statement into a variable in a Stored Procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related