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, Feb 21 '18 at 20:07
Possibly relevant: https://stackoverflow.com/questions/22372359/how-to-declare-global-variable-in-sql-server — xQbert, Feb 21 '18 at 20:27
@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, Feb 21 '18 at 20:39

score 1 · Accepted Answer · answered Feb 21 '18 at 20:19

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 '18 at 20:19

Tab Alleman

31,483
7
36
52

score 1 · Answer 2 · edited Feb 21 '18 at 20:50

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

SQL Server: how to use variables

2 Answers2