locked
Creating a Stored Procedure RRS feed

  • Question

  • To all

    I'm trying to create a stored procedure. The model is simple enough, three variables that are going to be created and then later called to thus return a number. The code I wrote was:-

    USE SUNDB44
    CREATE PROCEDURE dbo.uspQuickAmount
            @AccountCode char(15),
    @CostCentre char(15),
    @Period int
    AS
            SET NOCOUNT ON;

    SELECT ACCNT_CODE,ANAL_T1,PERIOD,AMOUNT
    FROM dbo.SALFLDGSCP
    WHERE ACCNT_CODE = @AccountCode,
    AND ANAL_T1 = @CostCentre,
    AND PERIOD = @Period,
    AND ANAL_T7 = 'SCP';
    GO

    However, I am getting the 'error line' underneath the 'CREATE PROCEDURE'

    The Message says: 'CREATE PROCEDURE' must be the only statement in the batch.

    I am guessing that it is objecting to the inclusion of the SELECT statement within the actual Procedure.

    What am I doing wrong?

    Thanks

    Mark Taylor

    Thursday, March 8, 2018 1:51 PM

Answers

  • The problem is your USE before the CREATE. I.e., you need GO after the USE command:

    USE SUNDB44

    GO
    CREATE PROCEDURE dbo.uspQuickAmount
            @AccountCode char(15),...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, March 8, 2018 1:54 PM
  • Hi,

    Try with a GO after Use SUNDB44.

    USE SUNDB44

    GO


    CREATE PROCEDURE dbo.uspQuickAmount
            @AccountCode char(15),
    @CostCentre char(15),
    @Period int
    AS
            SET NOCOUNT ON;

    SELECT ACCNT_CODE,ANAL_T1,PERIOD,AMOUNT
    FROM dbo.SALFLDGSCP
    WHERE ACCNT_CODE = @AccountCode,
    AND ANAL_T1 = @CostCentre,
    AND PERIOD = @Period,
    AND ANAL_T7 = 'SCP';
    GO


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, March 8, 2018 1:58 PM
    Answerer

All replies

  • The problem is your USE before the CREATE. I.e., you need GO after the USE command:

    USE SUNDB44

    GO
    CREATE PROCEDURE dbo.uspQuickAmount
            @AccountCode char(15),...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, March 8, 2018 1:54 PM
  • Hi,

    Try with a GO after Use SUNDB44.

    USE SUNDB44

    GO


    CREATE PROCEDURE dbo.uspQuickAmount
            @AccountCode char(15),
    @CostCentre char(15),
    @Period int
    AS
            SET NOCOUNT ON;

    SELECT ACCNT_CODE,ANAL_T1,PERIOD,AMOUNT
    FROM dbo.SALFLDGSCP
    WHERE ACCNT_CODE = @AccountCode,
    AND ANAL_T1 = @CostCentre,
    AND PERIOD = @Period,
    AND ANAL_T7 = 'SCP';
    GO


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, March 8, 2018 1:58 PM
    Answerer
  • Perfect, Thanks

    Mark Taylor

    Thursday, March 8, 2018 2:00 PM
  • Got it, thanks
    Thursday, March 8, 2018 2:05 PM