none
Msg 137, Level 15, State 2, Line 8 / Must declare the scalar variable "@Interest".

    Question

  • Hi guys!

     

    I have written the following store procedure in SQL server express 2005. But I get the above result when I parse it. Anyone for a tip?

     

    Thanks

     

    Jean-Philippe

     

    CREATE PROCEDURE procInsertClient

    -- Add the parameters for the stored procedure here

    @ClientID smallint OUTPUT,

    @FirstName nchar(20),

    @LastName nchar(20),

    @Phone nchar(15),

    @Email nchar(30),

    @Interest text,

    @Newsletter char(3)

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    INSERT INTO ClientHeader (FirstName, LastName, Phone, Email)

    VALUES (@FirstName, @LastName, @Phone, @Email)

    END

    GO

    BEGIN

    INSERT INTO ClientDetails (Interest, Newsletter)

    VALUES (@Interest, @Newsletter)

    END

    GO

    Thursday, July 12, 2007 9:02 PM

Answers

  • Your stored procedure definition ends with the first GO statement.

    Remove the lines END   GO  BEGIN in the middle so the whole SP is bracketed by a single BEGIN...END pair

    Thursday, July 12, 2007 9:10 PM
  • It is the GO:

     

    END

    GO

    BEGIN

    INSERT INTO

     

    GO seperates batches and variables are scoped to batches.  So if you want the insert to be part of the process, you need to remove the GO.

     

    I don't understand the two inserts anyhow.  How is the header related to the details?  Can you post your tables for the discussion?

    Thursday, July 12, 2007 9:13 PM

All replies

  • Your stored procedure definition ends with the first GO statement.

    Remove the lines END   GO  BEGIN in the middle so the whole SP is bracketed by a single BEGIN...END pair

    Thursday, July 12, 2007 9:10 PM
  • It is the GO:

     

    END

    GO

    BEGIN

    INSERT INTO

     

    GO seperates batches and variables are scoped to batches.  So if you want the insert to be part of the process, you need to remove the GO.

     

    I don't understand the two inserts anyhow.  How is the header related to the details?  Can you post your tables for the discussion?

    Thursday, July 12, 2007 9:13 PM
  • I got a bit confused to be honnest. I should have used a join.

     

    Anyway, I have sorted the problem, Iam only using 1 table since there aren't that many fields.

     

    Thanks a lot for your help.

     

    Jean-Philippe

    Friday, July 13, 2007 1:39 AM