none
Dynamic Branching

    Question

  • I'm trying to branch to a numeric label in a T-SQL script by deriving the label number at runtime, and it's not working. The following script returns an error "A GOTO statement references the label 'Branch2' but the label has not been declared." I understand from an earlier thread in this forum that sp_executesql doesn't recognize labels. I'm wondering if there's another way to accomplish this.

    	DECLARE 
    		@WhichBranch tinyint
    		,@SQLStr nvarchar(100);
    	SET @WhichBranch = 2
    	SET @SQLStr = N'GoTo Branch'+cast(@WhichBranch as nvarchar);
    	Print @SQLStr
    	EXEC sp_executesql @SQLStr
    	
    	Branch1:
    		Print 'I am at Branch 1'
    		RETURN
    	Branch2:
    		Print 'I am at Branch 2'
    		RETURN
    	Branch3:
    		Print 'I am at Branch 3'
    		RETURN
    	Branch4:
    		Print 'I am at Branch 4'
    		RETURN

    ...returns:

    GoTo Branch2
    Msg 133, Level 15, State 1, Line 1
    A GOTO statement references the label 'Branch2' but the label has not been declared.
    I am at Branch 1
    


    Darrell H Burns

    Friday, November 15, 2013 1:09 AM

Answers

  • "A GOTO statement references the label 'Branch2' but the label has not been declared." I understand from an earlier thread in this forum that sp_executesql doesn't recognize labels. I'm wondering if there's another way to accomplish this.

    You can use GOTO within the dynamic SQL batch but the scope is limited to labels within the dynamic batch; you can't branch out of the dynamic script.

    I'm not sure what your actual use case is but you could encapsulate the code within each branch in a separate proc.

    CREATE PROC dbo.usp_Branch1
    AS
    PRINT 'I am at Branch 1';
    RETURN;
    GO
    
    CREATE PROC dbo.usp_Branch2
    AS
    PRINT 'I am at Branch 2';
    RETURN;
    GO
    
    CREATE PROC dbo.usp_Branch3
    AS
    PRINT 'I am at Branch 3';
    RETURN;
    GO
    
    CREATE PROC dbo.usp_Branch4
    AS
    PRINT 'I am at Branch 4';
    RETURN;
    GO
    
    DECLARE 
    	  @WhichBranch tinyint
    	, @ProcName nvarchar(261);
    
    SET @WhichBranch = 2;
    SET @ProcName = N'dbo.usp_Branch' + cast(@WhichBranch as nvarchar(10));
    EXEC @ProcName;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, November 15, 2013 1:45 AM

All replies

  • "A GOTO statement references the label 'Branch2' but the label has not been declared." I understand from an earlier thread in this forum that sp_executesql doesn't recognize labels. I'm wondering if there's another way to accomplish this.

    You can use GOTO within the dynamic SQL batch but the scope is limited to labels within the dynamic batch; you can't branch out of the dynamic script.

    I'm not sure what your actual use case is but you could encapsulate the code within each branch in a separate proc.

    CREATE PROC dbo.usp_Branch1
    AS
    PRINT 'I am at Branch 1';
    RETURN;
    GO
    
    CREATE PROC dbo.usp_Branch2
    AS
    PRINT 'I am at Branch 2';
    RETURN;
    GO
    
    CREATE PROC dbo.usp_Branch3
    AS
    PRINT 'I am at Branch 3';
    RETURN;
    GO
    
    CREATE PROC dbo.usp_Branch4
    AS
    PRINT 'I am at Branch 4';
    RETURN;
    GO
    
    DECLARE 
    	  @WhichBranch tinyint
    	, @ProcName nvarchar(261);
    
    SET @WhichBranch = 2;
    SET @ProcName = N'dbo.usp_Branch' + cast(@WhichBranch as nvarchar(10));
    EXEC @ProcName;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, November 15, 2013 1:45 AM
  • OK, I get it.

    Thanx Dan!


    Darrell H Burns

    Friday, November 15, 2013 2:37 AM
  • Have you ever read "GOTO Considered Harmful" by Dijkstra? It is one of the classics of Computer Science and has been required in every software engineering course. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, November 15, 2013 3:26 AM
  • Have you ever read "GOTO Considered Harmful" by Dijkstra? It is one of the classics of Computer Science and has been required in every software engineering course. 

    DarrellDoesData, I am with --CELKO-- here, you should not use GOTO statements. Right now you are printing the GOTO statements so in this case you can debug incase of any error. But in real time scenarios we don't print statements in fact we use nocount to maximize the performance. I suggest you to use any other approach if possible.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 7:27 AM
  • I am not using the GoTo statements in a client application. I'm using it in a testing/debugging script I've created for my own use.

    Thanks for the advice.


    Darrell H Burns

    Saturday, November 16, 2013 11:04 PM
  • Celko,

    I'm sure you're a smart guy but I for one don't appreciate being made to look foolish by your arrogant self-serving advice.


    Darrell H Burns

    Saturday, November 16, 2013 11:10 PM