CODEWITHSUNDEEP
sqlsql-server
thecodeexplorer
thecodeexplorer

Reputation: 373

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

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.

Upvotes: 2

Views: 7157

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You should use SET

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

Upvotes: 6

Related Questions