|
|
Error Message
Server: Msg 133, Level 15, State 1, Line 2
A GOTO statement references the label '<Label Name>' but
the label has not been declared.
Causes
The GOTO statement, which is one of SQL Server’s Transaction-SQL control-of-flow language, causes the execution of a Transact-SQL batch to jump to a label. None of the statements between the GOTO statement and the label are executed. The GOTO statements and labels can be used anywhere within a procedure, batch or statement block and they can be nested.
|
Use the GOTO statement sparingly as excessive use of the GOTO statement can make it difficult to understand the logic of a Transact-SQL batch. The logic implemented using GOTO can almost always be implemented using other control-of-flow statements. The GOTO statement is best used for breaking out of deeply nested control-of-flow statements.
The label name is defined using the following syntax:
Label_Name:
The label that is the target of a GOTO identifies only the target of the jump. The label does nothing to isolate the statements following it from the statements immediately before it. Any user executing the statements immediately before the label skips the label and executes the statements after the label. This happens unless the statement immediately preceding the label is itself a control-of-flow statement, such as a RETURN or another GOTO.
As the message suggests, this error message occurs when a label is used in a GOTO statement but the label is not defined anywhere within the script block or stored procedure. Here are a few examples of how this error is encountered:
The following script uses the GOTO statement and specifies a label that has not been defined within the statement block, and therefore generating the error:
-- Case #1 : Missing Label
DECLARE @UserName VARCHAR(20)
SET @UserName = 'SQLServer'
IF NOT EXISTS (SELECT 'X' FROM [dbo].[User]
WHERE [UserName] = @UserName)
GOTO InvalidUser
Msg 133, Level 15, State 1, Line 7
A GOTO statement references the label 'InvalidUser' but the label has not been declared.
The following script uses the GOTO statement but specifies a label that does not exist because it is incorrectly spelled:
-- Case #2 : Misspelled Label
DECLARE @TransactionDate DATETIME
IF @TransactionDate IS NULL OR @TransactionDate > GETDATE()
GOTO InvaliDate
InvalidDate:
PRINT 'Transaction date required and must be before current date.'
GO
Msg 133, Level 15, State 1, Line 9
A GOTO statement references the label 'InvaliDate' but the label has not been declared.
The following script uses the GOTO statement but references a label that is beyond the scope of the statement block as it is beyond the GO command:
-- Case #3 : Labels Across Query Batches
DECLARE @UserName VARCHAR(20)
DECLARE @TransactionDate DATETIME
IF @UserName IS NULL OR @UserName = ''
GOTO WithError
IF NOT EXISTS (SELECT 'X' FROM [dbo].[User]
WHERE [UserName] = @UserName)
GOTO WithError
IF @TransactionDate IS NULL OR @TransactionDate > GETDATE()
GOTO WithError
GO
WithError:
PRINT 'An error was encountered processing the transaction.'
GO
Msg 133, Level 15, State 1, Line 14
A GOTO statement references the label 'WithError' but the label has not been declared.
Solution / Work Around:
As the message suggests, make sure that the label specified in the GOTO statement is defined within the statement block or within the stored procedure wherein it is used.
Using the first example earlier, here’s an updated script that defines the missing label:
-- Case #1 : Define the Missing Label
DECLARE @UserName VARCHAR(20)
SET @UserName = 'SQLServer'
IF NOT EXISTS (SELECT 'X' FROM [dbo].[User]
WHERE [UserName] = @UserName)
GOTO InvalidUser
InvalidUser:
PRINT 'Invalid User Name'
GO
Using the second example above, here’s an updated script which corrects the incorrectly spelled label specified in the GOTO statement:
-- Case #2 : Verify Label Name
DECLARE @TransactionDate DATETIME
IF @TransactionDate IS NULL OR @TransactionDate > GETDATE()
GOTO InvalidDate
InvalidDate:
PRINT 'Transaction date is required and must be before current date.'
GO
Lastly using the third example, here’s how the script will look like with the BEGIN … END block put into place and avoiding the error:
-- Case #3 : Labels Across Query Batches
DECLARE @UserName VARCHAR(20)
DECLARE @TransactionDate DATETIME
IF @UserName IS NULL OR @UserName = ''
GOTO WithError
IF NOT EXISTS (SELECT 'X' FROM [dbo].[User]
WHERE [UserName] = @UserName)
GOTO WithError
IF @TransactionDate IS NULL OR @TransactionDate > GETDATE()
GOTO WithError
WithError:
PRINT 'An error was encountered processing the transaction.'
GO
|