Post reply

Multiple SQL Statements in One Stored Procedure

  • January 27, 2014 at 4:36 am

    #282739

    I have never written a Stored Procedure before.

    I want to execute a SQL Stored Procedure that executes the following SQL commands:

    SELECT SUM(col3) FROM table1;

    SELECT SUM(col4) FROM table1;

    SELECT SUM(col5) FROM table1;

    DELETE FROM table1;

    INSERT INTO table1 (col3,col4,col5) VALUES (SUM(col3),SUM(col4),SUM(col5));

  • January 27, 2014 at 4:40 am

    #1683477

    Edit: optimised.

    CREATE PROCEDURE doStuff

    AS

    DECLARE @Total1 INT, @Total2 INT, @Total3 INT;

    SELECT @Total1 = SUM(col3), @Total2 = SUM(col4), @Total3 = SUM(col5) FROM table1;

    DELETE FROM table1;

    INSERT INTO table1 (col3,col4,col5) VALUES (@Total1, @Total2, @Total3);

    GO

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2014 at 4:43 am

    #1683478

    Gail was apparently fast than me, but since I typed out that whole sp, I'm going to post it anyway 🙂

    CREATE PROC MyLittleSumProc AS

    DECLARE @sum1 BIGINT, @sum2 BIGINT, @sum3 BIGINT;

    SELECT

    @sum1 = SUM(col3)

    ,@sum2 = SUM(col4)

    ,@sum3 = SUM(col5)

    FROM table1;

    DELETE FROM table1;

    INSERT INTO table1 (col3,col4,col5) VALUES (@sum1,@sum2,@sum3);

    GO

    EXEC MyLittleSumProc;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 27, 2014 at 4:55 am

    #1683484

    Thank you very much!

  • January 27, 2014 at 5:50 am

    #1683495

    I entered this in as below. The error code was:

    Incorrect syntax near 'GO'.

    CREATE P*****

    CREATE PROCEDURE CollapseToSUMs AS

    DECLARE @col1 BIGINT, @col2 BIGINT, @col3 BIGINT;

    SELECT

    @col1 = SUM(col1)

    ,@col2 = SUM(col2)

    ,@col3 = SUM(col3)

    FROM table1;

    DELETE FROM test;

    INSERT INTO test (col1,col2,col3) VALUES (@col1,@col2,@col3);

    GO

  • January 27, 2014 at 5:55 am

    #1683496

    I just ran it, no errors.

    What version of SQL Server, how are you executing that code?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2014 at 5:57 am

    #1683498

    SQL Server 2008 on 1and1 server

  • January 27, 2014 at 6:00 am

    #1683500

    How are you executing the code?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2014 at 6:10 am

    #1683504

    I went to my 1and1 server. Clicked on MS SQL then Stored Procedures. Selected New Procedure. I copied and pasted the SQL code into the box. Hit Create.

    Error:

    Error -2147217900

    Incorrect syntax near 'GO'.

    CREATE P*****

  • January 27, 2014 at 6:13 am

    #1683506

    hi,

    just a quick note to philosophae:

    in the new query you wrote you're summing from table1 and deleting / inserting in table test; this is not what you ment to do in your very first query.

    so the next question, does table1 exist?

  • January 27, 2014 at 6:21 am

    #1683508

    In that case, remove the GO. GO is a management studio command, designates the end of a batch. If you're using some other tool to access SQL Server, send one batch at a time and don't specify GO.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply