How can I declare a variable in T-SQL and use it in multiple statements?
For example:
DECLARE @STRDT DATE
SET @STRDT '2017-01-01'
SELECT TOP(10) *
FROM TABLE1
WHERE START_DATE = @STRDT;
SELECT TOP(10) *
FROM TABLE2
WHERE START_DATE = @STRDT;
Right now, I am able to run the first statement correctly, but unable to use the declared variable in my second statement. I get an e error
Must declare @STRDT
when I am running the second select statement in SQL Server.
The OP does not indicate which client app they're using to execute the SQL statements. Probably SSMS, since that's the usual MS SQL Server tool? Or maybe Azure Data Studio?
In my case, I've been trying to execute such multi-statement batches in DBeaver for MS SQL Server T-SQL. As noted here, you can (sometimes) make this work by surrounding the statements with BEGIN and END. For example, for the simple query below, you can execute the following using Ctrl+Enter or Ctrl+\ anywhere in the BEGIN-END block. It will open two tabs since there are two SELECT statements.
BEGIN
DECLARE @StartDate datetime='1/1/2023 12:00:00 AM',
@EndDate datetime='12/31/2023 12:00:00 AM';
SELECT @StartDate as [Start Date];
SELECT @EndDate as [End Date];
END
For more complex queries, it can be necessary to position the cursor on the BEGIN statement and then use Ctrl+Enter or Ctrl+\. (Or, alternatively, highlight the entire BEGIN-END block and press Alt+X.) In particular, I've found this to be necessary when the SQL statement(s) contain with cte1 (...)... Common Table Expressions.
I've also been running into more complex batches of SQL statements that execute successfully in SSMS, but that DBeaver rejects because it complains that there is a syntax error. I have not found a solution to that problem.
Bottom line with respect to DBeaver: it seems it simply won't execute some MS Sql Server T-Sql multi-statement batches that SSMS and Azure Data Studio execute correctly.
Variables exist only within a certain scope which is within a statement that is being run. Therefore, when you have logic like this in a stored procedure it'll be run within the same scope because it's the same batch so the variable will "work" for both statements. It's the same thing when you want to run it manually - you have to run both statements together in order to get what you want. That's simply how it works and if you run both statements at the same time - the variable "works" in both - or multiple - statements.
To read up more on them, consult the following sources:
As pointed out in the comments, its not working for second statement because of the presence of the semi-colon ;
If you want to use the variable in both statements, remove the semi-colon between the two statements and run the entire code.
SET @STRDT = '2017-01-01'SELECTand execute only thatSELECT- then of course, the@STRDTis never declared. Variables in T-SQL are not persisted - you must declare them before each use