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

Answer 1

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

Answer 2

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?

Answer 3

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