Variable Declaration Failure
-
Hi,
I'm have declared a simple variable, and on a very next line I'm trying to use it to SET a value... but I'm getting error:
Declare @test-2 VARCHAR(100)
Set @test-2 = 'This is my name'
SELECT @test-2
GO
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@test"
This is with without any 'GO' in the middle, and without dynamic SQL statements... but when I select all statements together, it works fine
What could be the reason please? i've spent 2 hours but couldn't fix. What am I doing wrong please?
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
Thanks and Regards,
K
-
The way Eirikur does it is my preference too; no reason for a SET statement if you know the value of the variable when you declare it.
To expand on what pietlinden said, the life span of a variable is from when a query is executed to the time it is done executing. E.g. in SQL Server Management Studio - from the time you click the execute button (or press F5) until when you get the message that reads "Query executed successfully" or "Query completed with errors."
When learning SQL Server some very important concepts to understand were: batches, sessions, transactions and connections.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
My understanding is, once you define the variable it stays in memory as long as same query screen is opened... and it perfectly worked for me in the past, like this.
Declare @test-2 VARCHAR(100) --Executed separately by selection, completed successfully.
Set @test-2 = 'This is my name' --Executed separately by selection, completed successfully.
SELECT @test-2 --Executed separately by selection, completed successfully.
...but now, if and only if, i select them all together and then execute, they execute successfully... if i execute them one by one as i mentioned above, they don't!!!
I need to use the same variables down below in code as well, I cannot define them one by one every time i need to use that variable unless i use GO or EXEC, or else it'll lose its purpose and every time definition will be like a new definition.
This behavior is strange and it did not happen in the past (as long as my memory supports me, and I'm using SQL Server for over 10 months now). Now i've resumed my learning after 2 months gap and is behaving like this.
Below I'm pasting from my test code. Everything in comments... i executed and each and every line separately for testing purposes, and definition as only on the first line. It's not only about using the 'SET' things, it's about using the same definition used above... later down the road as well.
Thanks,
K
DECLARE @char VARCHAR(10), @char0 CHAR(5), @char1 CHAR(25), @dec DEC(10,3), @datetime2 DATETIMEOFFSET --PAGE 40 ONWARDS
SET @char = 12345
SET @char0 = 'going'
SET @char1 = 'Myself is going Cork'
SET @dec = 1000.5 * .529374
SET @datetime2 = SYSDATETIMEOFFSET() --CONVERT(TIME, SYSDATETIME()) --'12-10-22 12:06:25.768'
SELECT
CAST (@char AS INT)+5 AS "CAST", CONVERT (INT, @char) AS "CONVERT", @char+1 AS "CHAR+INT",
@char + COALESCE(', ' + @char1, '-1') AS "COALESCE", --ISNULL same as Coalesce, but limited to only 1 AR
--@dec AS "DEC"
--@datetime2 AS "DATETIME2"
--SUBSTRING(@char1, CHARINDEX(@char0, @char1), len(@char0))
--CHARINDEX(',',@char + COALESCE(N', ' + @char1, N'')) AS "CHAR INDEX", --shows the position of first occurance of 1st AR
--SUBSTRING(@char1,2,3) AS "SUBSTRING", --Starting from characters mentioned in AR2 + count till AR3
--PATINDEX('%[g-i]%',@char1) AS "PATINDEX" --Same as above, except it accepts wildcard range only
--LEN (@char1) AS "LEN", DATALENGTH(@char1) AS "DATALENGTH" --**DataLength is the length of the data size
--STUFF(@char1 ,12 ,5 ,'Stuff') --REPLICATE('@char1',2) --REPLACE(@char1 ,'r' ,'1')
--FORMAT(1759,'00000'), UPPER(@char1), LOWER(@char1), LTRIM(@char1), RTRIM(@char1)
--NULLIF(@char, @char1) AS 'NullIF', --Returns NULL if both are equal, else returns first value
-
gfx99ne (8/4/2016)
My understanding is, once you define the variable it stays in memory as long as same query screen is opened... and it perfectly worked for me in the past, like this.Declare @test-2 VARCHAR(100) --Executed separately by selection, completed successfully.
Set @test-2 = 'This is my name' --Executed separately by selection, completed successfully.
SELECT @test-2 --Executed separately by selection, completed successfully.
Variables don't stay in memory. Their scope is only the current execution.
From Bol
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
...
Variables have local scope and are only visible within the batch or procedure where they are defined.
EDIT: What you mention was probably true in Oracle where you can define environmental variables. I don't have much experience on that to assure it.
-
Maybe I've picked up something wrong, but you experience people could not be wrong.
Thank you all, for your explanations.
Regards,
K
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply