none
GO in a Stored Procedure

    Question

  • I want to include GO statements to execute a large stored procedure in batches, how can I do that?
    • Edited by PMK123 Friday, June 19, 2015 3:53 AM
    Friday, June 19, 2015 3:52 AM

Answers

  • Hi,

    Are you looking at implementing some thing like this as in the code below.

    ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
    @projectid int
    AS
     Set Nocount On 
    Begin
    create table #ebdindex (
    [indexid] [int] IDENTITY (1, 1) NOT NULL ,
    [stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
    [trans] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
    [status] [int]
    ) 
    create table #residinpages (
    [indexid] [int],
    [stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
    [pageid] [int]
    ) 
    create table #residinpages2 (
    [indexid] [int],
    [pageid] [int]
    ) 
    Go
    
    insert into #ebdindex
    Select stringid, trans, status from edbs e where
    e.lcid='en' and e.projectid=@projectid
    Go
    
    insert into #residinpages
    select distinct e.indexid, e.stringid, CASE
    WHEN s.pageid IS NULL then 0
    ELSE s.pageid
    END as pageid
    from #ebdindex e, 
    stringidsinpages s, pages p
    where e.stringid *=s.stringid
    order by e.indexid
    Go
    
    declare @indexId nchar(128)
    declare @pageId nchar(128)
    declare @RowNum int
    declare @maxid int
    
    select top 1 @indexId=indexID, @pageid=pageid from #residinpages
    set @RowNum = 0 
    
    select @maxid = max(indexid) from #ebdindex
    WHILE @RowNum < @maxid
    BEGIN
    set @RowNum = @RowNum + 1
    insert into #residinpages2 values( @indexId, @pageid)
    select top 1 @indexId=indexID, @pageid=pageid from #residinpages
    where indexId > @indexId
    END
    GO
    select e.indexid,e.stringid, e.trans, r.pageid, e.status from #residinpages2 r, #ebdindex e
    where e.indexid=r.indexid
    drop table #residinpages2 
    drop table #residinpages
    drop table #ebdindex
    GO
    End

    Hope this helps

    Thanks

    Bhanu

    • Marked as answer by PMK123 Thursday, July 23, 2015 2:34 PM
    Friday, June 19, 2015 3:58 AM
  • First of all "GO" is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here: http://sqlwithmanoj.com/2012/05/06/go-is-not-a-valid-tsql-statement/

    Secondly "GO" is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP.

    Now, why do you want to use GO within an SP?

    If the SP is large, then try creating multiple SP and execute them in sequence.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Friday, June 19, 2015 12:43 PM

All replies

  • Hi,

    Are you looking at implementing some thing like this as in the code below.

    ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
    @projectid int
    AS
     Set Nocount On 
    Begin
    create table #ebdindex (
    [indexid] [int] IDENTITY (1, 1) NOT NULL ,
    [stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
    [trans] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
    [status] [int]
    ) 
    create table #residinpages (
    [indexid] [int],
    [stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
    [pageid] [int]
    ) 
    create table #residinpages2 (
    [indexid] [int],
    [pageid] [int]
    ) 
    Go
    
    insert into #ebdindex
    Select stringid, trans, status from edbs e where
    e.lcid='en' and e.projectid=@projectid
    Go
    
    insert into #residinpages
    select distinct e.indexid, e.stringid, CASE
    WHEN s.pageid IS NULL then 0
    ELSE s.pageid
    END as pageid
    from #ebdindex e, 
    stringidsinpages s, pages p
    where e.stringid *=s.stringid
    order by e.indexid
    Go
    
    declare @indexId nchar(128)
    declare @pageId nchar(128)
    declare @RowNum int
    declare @maxid int
    
    select top 1 @indexId=indexID, @pageid=pageid from #residinpages
    set @RowNum = 0 
    
    select @maxid = max(indexid) from #ebdindex
    WHILE @RowNum < @maxid
    BEGIN
    set @RowNum = @RowNum + 1
    insert into #residinpages2 values( @indexId, @pageid)
    select top 1 @indexId=indexID, @pageid=pageid from #residinpages
    where indexId > @indexId
    END
    GO
    select e.indexid,e.stringid, e.trans, r.pageid, e.status from #residinpages2 r, #ebdindex e
    where e.indexid=r.indexid
    drop table #residinpages2 
    drop table #residinpages
    drop table #ebdindex
    GO
    End

    Hope this helps

    Thanks

    Bhanu

    • Marked as answer by PMK123 Thursday, July 23, 2015 2:34 PM
    Friday, June 19, 2015 3:58 AM
  • Please read this thread

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d015786e-a69b-4fc1-b752-d10a85c2399e/use-of-go-in-a-stored-procedure?forum=transactsql

    And this link

    https://msdn.microsoft.com/en-us/library/ms188037.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Friday, June 19, 2015 4:15 AM
    Answerer
  • GO is not an SQL statement. GO is something which is consumed by the query tool, to split up text you have selected in batches. If you say:

    SELECT 1
    SELECT 2

    SQL Server will see exactly this. But you say:

    SELECT 1
    GO
    SELECT 2

    SQL Server will first see - and execute - "SELECT 1". Once the statement has completed, SSMS will send "SELECT 2" to SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 19, 2015 8:51 AM
  • No can do. The definition of the proc is within a batch and GO is the batch separator. If you instead tell us *why* you need this, we might be able to suggest something.

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, June 19, 2015 11:24 AM
    Moderator
  • First of all "GO" is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here: http://sqlwithmanoj.com/2012/05/06/go-is-not-a-valid-tsql-statement/

    Secondly "GO" is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP.

    Now, why do you want to use GO within an SP?

    If the SP is large, then try creating multiple SP and execute them in sequence.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Friday, June 19, 2015 12:43 PM
  • Want to include GO in the large stored procedure to avoid out of memory exception in the hosted environment.
    Friday, June 19, 2015 2:00 PM
  • bhanu_nz,

    Yes, that is exactly what I was looking for, but I am not sure how to include GO from the script.

    • Edited by PMK123 Friday, June 19, 2015 2:05 PM
    Friday, June 19, 2015 2:01 PM
  • "out of memory" is not an SQL Server error, but comes from SSMS.

    In any case, you have now been told that GO in a stored procedure does not make sense. You could break up the procedure in several if the size really is a problem.

    It could help if you post the full error message you get.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 19, 2015 6:45 PM