Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post .

Closed 4 days ago .

Depends on the SQL - some of the dialects don't provide a useful mechanism for flow control other than GOTO.

GOTO is generally bad form.

Not in production code but for testing could be ok.

For example, wanting to provide regression testing for a stored procedure where the "common bit" is the call to the procedure being tested and debug statements.

declare @test int;
set @test = 1;
goto tests
common:
print 'common bit'
tests:
if @test = 1 print '1';
if @test = 2 print '2';
if @test = 3 print '3';
set @test = @test + 1;
if @test <= 3 goto common
print 'finished ' + cast(@test as varchar(5))
go  -- goto can not be used past go!
Being a t-sql noob I was hoping for procedure or function to declare within scope to do the "common bit" but this was the best I could come up with after much googling.  Why would you have to setup a stored procedure for every bit of code you want to re-use.  Especially for non production work.
                You make a great point about using small bits of functionality using GOTO. :-)  We need Lambda expressions in T-SQL! ;-)
– Norman H
                Jul 26, 2013 at 12:42
goto is keyword with its own feature.
We can use goto whenever it is required to jump directly to some level.
Lets take the example...
In my stored procedure I need to work with data taking in 4 temp tables.
at every level after inserting records in temp table I need to check whether records are present in this temp temp or not, if no records got inserted so instead of processing further I can directly jump down using goto.
label is the point where we should jump:
 CREATE TABLE #tmpMsNos (custPo CHAR(24))
    CREATE TABLE #tmpValidBilltos (billto CHAR(12))
    CREATE TABLE #tmpOrders (
        fh_pkey INT
        ,fh_id CHAR(8)
        ,custPo CHAR(24)
        ,lastchOfCustInsert DATETIME
    CREATE TABLE #tmpOrdersFiltered (
        fh_pkey INT
        ,fh_id CHAR(8)
        ,custPo CHAR(24)
        ,lastchOfCustInsert DATETIME
        ,onbDate DATETIME
        ,rapDate DATETIME
    CREATE TABLE #tmpLoad (
        custPo CHAR(24)
        ,ld_pkey INT
        ,ld_wkpmpn CHAR(25)
        ,lda_barcode VARCHAR(30)
        ,ld_createdOn DATETIME
        ,ReceivedDate DATETIME
        ,DispatchedDate DATETIME
INSERT INTO #tmpMsNos
    SELECT cast(itemValue AS CHAR(24)) 
    FROM dbo.fn_array_to_table(@pMsNos, ',')
            NOT EXISTS (
                SELECT 1
                FROM #tmpMsNos
    BEGIN
        GOTO label
INSERT INTO #tmpValidBilltos
    SELECT CONVERT(CHAR(12), xm_doref)
    FROM xmlref x
    WHERE xm_element = 'THD-BoxtruckRequest'
        AND xm_attribute = 'THD-BoxtruckBillto'
            NOT EXISTS (
                SELECT 1
                FROM #tmpValidBilltos
    BEGIN
        GOTO label
INSERT INTO #tmpOrders
    SELECT fh.fh_pkey
        ,fh.fh_id
        ,fh.fh_custPo
        ,max(coc.ch_dt)
    FROM #tmpMsNos msNos
    INNER JOIN fcfgthd fh ON msNos.custPo = fh.fh_custPo
    INNER JOIN #tmpValidBilltos bt ON bt.billto = fh.fh_bt_id
    LEFT JOIN chofcust coc ON coc.ch_fhpkey = fh.fh_pkey
    WHERE fh.fh_statcode NOT IN (
            98 --CAN
            ,99 --DEL
        AND fh.fh_ship_dt > @startDate
    GROUP BY fh.fh_pkey
        ,fh.fh_id
        ,fh.fh_custPo
            NOT EXISTS (
                SELECT 1
                FROM #tmpOrders
    BEGIN
        GOTO label
INSERT INTO #tmpOrdersFiltered
    SELECT t.fh_pkey
        ,t.fh_id
        ,t.custPo
        ,t.lastchOfCustInsert
        ,MAX(cocONB.ch_dt)
        ,MAX(cocRAP.ch_dt)
    FROM (
        SELECT tmpO.fh_pkey
            ,tmpo.fh_id
            ,tmpO.custPo
            ,tmpO.lastchOfCustInsert
        FROM #tmpOrders tmpO
        INNER JOIN (
            SELECT custpo
                ,max(lastchOfCustInsert) AS MaxInserteddate
            FROM #tmpOrders
            GROUP BY custpo
            ) tmpOgrouped ON tmpO.custpo = tmpOgrouped.custpo
            AND tmpO.lastchOfCustInsert = tmpOgrouped.MaxInserteddate
        ) AS t
    LEFT JOIN chofcust cocRAP ON cocRAP.ch_fhpkey = t.fh_pkey
        AND cocRAP.ch_stat = 2 -- RAP --TODO: Add comment with status code like 98, 99 -- CAN, DEL for readability - Paresh
    LEFT JOIN chofcust cocONB ON cocONB.ch_fhpkey = t.fh_pkey
        AND cocONB.ch_stat = 5 -- ONB --TODO: Add comment with status code like 98, 99 -- CAN, DEL for readability - Paresh
    GROUP BY t.fh_pkey
        ,t.fh_id
        ,t.custPo
        ,t.lastchOfCustInsert
    --TODO: Take an exit if no order found into #tmpOrdersFiltered table, while taking a early exit make sure it doesn't break the calling code (C#)  - Paresh
            NOT EXISTS (
                SELECT 1
                FROM #tmpOrdersFiltered
    BEGIN
        GOTO label
INSERT INTO #tmpLoad
    SELECT o.custPo
        ,l.ld_pkey
        ,l.ld_wkpmpn
        ,la.lda_barcode
        ,max(coc.ch_dt)
        ,CASE ISNULL(w.xl_date, '')
            WHEN ''
                THEN o.rapDate
            ELSE w.xl_date
            END AS ReceivedDate
        ,CASE ISNULL(mm.me_ecpkey, '')
            WHEN ''
                THEN o.ONBDate
            ELSE NULL
            END AS DispatchedDate
    FROM #tmpOrdersFiltered o
    INNER JOIN fcload l ON l.ld_fhpkey = o.fh_pkey
    LEFT JOIN loadanc la ON la.lda_ldpkey = l.ld_pkey
    LEFT JOIN wkxaclog w ON w.xl_ldpkey = l.ld_pkey
    LEFT JOIN multiexceps mm ON mm.me_ldpkey = l.ld_pkey
        AND mm.me_ecpkey = @missingitemexcep
    LEFT JOIN chofcust COC ON coc.ch_ldpkey = l.ld_pkey
        AND coc.ch_stat = 64 -- 64= ILH
    GROUP BY o.custPo
        ,l.ld_pkey
        ,l.ld_wkpmpn
        ,la.lda_barcode
        ,w.xl_date
        ,o.rapDate
        ,mm.me_ecpkey
        ,o.ONBDate