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

Transact-SQL

jpchenot


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

Top

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

David Frommer


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






Top

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

Louis Davidson

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






Top

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

jpchenot

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


Top