SQL Server: how to use variables

Question

This is very much a beginner question, but how do variables actually work in SSMS? I have the following 3 statements:

DECLARE @p1 TABLE (col2 VARCHAR(30))

UPDATE t 
SET col2 = 'Peter', col3 = GETDATE()
OUTPUT CAST(INSERTED.col3 AS VARCHAR(30)) i
INTO @p1
WHERE col1 = 1

SELECT * FROM @p1

In SSMS, if I highlight all 3 statements and execute them together, the statements work as expected. But if I select & run them one at a time, I receive an error message

Msg 1087, Level 15, State 2, Line 71
Must declare the table variable "@p1"

Are T-SQL variables just available during specific execution? Is there another technique to actually "set" a variable to persist throughout a Session?

Not sure what your definition of "Session" is here. But variables have a scope in sql server. They are limited to the current batch. A batch is just any number of statements being executed. So when you click run (or hit F5) you are executing a batch. You can break this into multiple batches in SSMS by using the batch separator (default is GO). Then each batch will run independent of the others. So in your example above, if you run the final select by itself it has no variable to reference so you get the error. — Sean Lange
– Sean Lange, Commented Feb 21, 2018 at 20:07
@marc_s - I see you edited my question. How do I format my question so the Error Message in this specific case shows up in a separate box with a yellow background? — CarCrazyBen
– CarCrazyBen, Commented Feb 21, 2018 at 20:39

Tab Alleman · Accepted Answer · 2018-02-21 20:19:58Z

1

Yes, variables are only available for a given execution (or batch, as Sean's comment explains).

There is no setting or technique that will allow variables to persist for an entire session, but temp tables do.

answered Feb 21, 2018 at 20:19

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

Comments

Daniel Puiu · Accepted Answer · 2018-02-21 20:50:13Z

1

Sean Lange's comment above is correct. In this case it seems that you want to insert data into a table variable and then be able to use the data in the table later on. You could use a temp table to accomplish this and the table will be available until you drop it or close your query window.

Create table #p1 (col2 varchar(30))

UPDATE t SET col2 = 'Peter', col3 = getdate()
OUTPUT CAST(INSERTED.col3 AS varchar(30))i
into #p1
WHERE col1 = 1

select * from #p1

edited Feb 21, 2018 at 20:50

Daniel Puiu

9616 gold badges21 silver badges29 bronze badges

answered Feb 21, 2018 at 20:21

phoenix15

111 bronze badge

Collectives™ on Stack Overflow

SQL Server: how to use variables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related