Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1875
Members Online:
1
Guests Online:
94

Total Post History
Posts:
81447
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
konam534As

Other

FAQ
All times are UTC [ DST ]

Google Ads

Does the GO statement affect declared variables?

Microsoft SQL Server
Please wait...

Does the GO statement affect declared variables?

Postby Chopstik on Mon Jul 17, 2017 2:46 am

I'm working on a template that will enable our devs to use dynamic variables that will recognize the environment the code is being deployed to and assign accordingly. I've tried a few different variations of this but keep hitting different concerns. The last thing I've tried seems to have hit a roadblock that I believe is due to a GO statement which renders any variables after that point as unknown. Can someone advise as I can't seem to find confirmation one way or another beyond what I see when I attempt to validate the code? Thanks.

  1. DECLARE @EnvServerName NVARCHAR(50) = @@ServerName  --The length of the nvarchar depends on how long the server name is.
  2.  
  3. --SELECT @EnvServerName AS 'ServerName'
  4. --This declares the variables to be set according the server instance being executed against. It can have as many or few as needed.
  5. DECLARE @ServerName NVARCHAR(25) = '' --The length of the nvarchar depends on how long the variable will be. In this case, 25 characters max.
  6. DECLARE @MHP_Root NVARCHAR(70) = '' --The length of the nvarchar depends on how long the variable will be. In this case, a URL over 50 characters.
  7. DECLARE @TPA_Root NVARCHAR(70) = '' --The length of the nvarchar depends on how long the variable will be. In this case, a URL.
  8. DECLARE @EHR_Root NVARCHAR(70) = '' --The length of the nvarchar depends on how long the variable will be. In this case, a URL.
  9. --DECLARE @strSQL NVARCHAR(255) = '' --The length of the nvarchar depends on how long the variable will be. In this case, it's a URL so will need more characters.
  10.  
  11. --This will set the values of the variables according to the instance being executed against.
  12. IF @EnvServerName = 'Server1' --This is the * instance name
  13.     BEGIN
  14.         SET @ServerName = 'Server1'
  15.         SET @MHP_Root = 'URL1a'
  16.         SET @TPA_Root = 'URL1b'
  17.         SET @EHR_Root = 'URL1c'
  18.     END
  19. ELSE IF @EnvServerName = 'Server2' --This is the * instance name
  20.     BEGIN
  21.         SET @ServerName = 'Server2'
  22.         SET @MHP_Root = 'URL2a'
  23.         SET @TPA_Root = 'URL2b'
  24.         SET @EHR_Root = 'URL2c'
  25.     END
  26. ELSE IF @EnvServerName = 'Server3' --This is the * instance name
  27.     BEGIN
  28.         SET @ServerName = 'Server3'
  29.         SET @MHP_Root = 'URL3a'
  30.         SET @TPA_Root = 'URL3b'
  31.         SET @EHR_Root = 'URL3c'
  32.     END
  33. ELSE IF @EnvServerName = 'Server4' --This is the * instance name
  34.     BEGIN
  35.         SET @ServerName = 'Server4'
  36.         SET @MHP_Root = 'URL4a'
  37.         SET @TPA_Root = 'URL4b'
  38.         SET @EHR_Root = 'URL4c'
  39.     END
  40. ELSE IF @EnvServerName = 'Server5' --This is the * instance name
  41.     BEGIN
  42.         SET @ServerName = 'Server5'
  43.         SET @MHP_Root = 'URL5a'
  44.         SET @TPA_Root = 'URL5b'
  45.         SET @EHR_Root = 'URL5c'
  46.     END
  47. ELSE --Set a blank value to test against and fail the SP here if this is executed against the wrong DB
  48.     BEGIN
  49.         SET @ServerName = 'Invalid'
  50.         SET @MHP_Root = 'Invalid'
  51.         SET @TPA_Root = 'Invalid'
  52.         SET @EHR_Root = 'Invalid'
  53.     END
  54.  
  55. IF @ServerName = 'Invalid' OR @MHP_Root = 'Invalid' OR @TPA_Root = 'Invalid' OR @EHR_Root = 'Invalid'
  56.     BEGIN
  57.         GOTO StopProcessingError;
  58.     END
  59.    
  60.  
  61.  
  62.  
  63. Print 'Start'
  64. --Update Statements here
  65. Print 'End'
  66. Go --Where I think the problem is occurring
  67.  
  68. print 'Key'
  69. if not exists (select * from table1 where app_key='value' and server=@ServerName)
  70. begin
  71.         insert into table1 (server,app_name,app_key,key_type,key_value,is_key_enabled,comments)
  72.             values(@ServerName,'test','test2','string','test3','Y','')
  73. end
  74. else
  75. begin   
  76.         update table1 set key_value='stringValue' where server=@ServerName and app_key='varName'
  77. end
  78. Go
  79.  
  80. --Much more code down below that continues to generate errors with variables needing to be declared that are already declared.
  81.  
  82. StopProcessingError:
  83.     IF @ServerName = 'Invalid'
  84.     BEGIN
  85.         RAISERROR('Executed in invalid database',1,-1)
  86.     END


My errors read as follows:
Msg 133, Level 15, State 1, Line 68
A GOTO statement references the label 'StopProcessingError' but the label has not been declared.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@ServerName".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@ServerName".


I can guess that the first one is also because I'm trying to use the GOTO statement and then have other GO statements in the code but not sure how to work around that just yet. I'd prefer to avoid a GOTO but it was the only way I could see to ensure that code termination would occur if it was accidentally run on the wrong server (as RAISERROR won't firmly terminate all the time). But the fact that the @ServerName variable has to be redeclared is, I'm pretty sure, due to the use of the GO statement at the end of each code segment in order to ensure execution. Is there another way around this because I am working with what the devs have provided and this is their standard (which I can't dictate at this time). Any advice would be greatly appreciated. Thanks.
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated

Re: Does the GO statement affect declared variables?

Postby Naomi on Wed Aug 09, 2017 9:52 pm

Yes, GO is a SSMS batch terminator so everything after the go is a new batch and all variables will not be visible either.

I suggest to use TRY/CATCH/FINALLY functionality instead that was introduced in SQL 2008 and improved in SQL 2012 with the THROW statement.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI

Re: Does the GO statement affect declared variables?

Postby genomon on Fri Aug 11, 2017 11:02 pm

So looks like you solved this?
I always save "GO" for the end of the proc.
The semicolon will terminate steps as such, but I agre "TRY...CATCH"
is the way to "go".
Could be transactions would be helpful as well in the event of a rollback situation?
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: Does the GO statement affect declared variables?

Postby Chopstik on Mon Aug 14, 2017 3:10 am

Transactions are a no go for the devs - don't ask me why.

As a compromise, they have agreed to move the relevant code using the declared variables to the beginning of their scripts which should avoid the GO statements until later. It's a rough workaround but it'll do for now. I just need this off my desk... :o
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

WAR IS PEACE • FREEDOM IS SLAVERY • IGNORANCE IS STRENGTH

Winston Smith
User avatar
Chopstik
LTD Admin
LTD Admin
LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346LTD Silver - Rating: 346
LTD Silver - Rating: 346
 
Posts: 4316
Joined: Thu Oct 11, 2007 2:04 pm
Unrated