SQL Server Performance Forum – Threads Archive

Can someone see something wrong with this sql?

I’m trying to create this proc and I’m getting the error message:
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@AltCode".
CREATE PROC InsertPmcdRecord
(@AltCode nvarchar(20),
@Contract nvarchar(20),
@Cost float,
@CustomerNum int,
@ExpDate datetime,
@ListPrice float,
@MpAccrual int,
@MpQty int)
AS
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON
GO ALTER DATABASE ContractTools SET RECOVERY SIMPLE
GO INSERT INTO Pmcd (
PmcdAltCode,
PmcdContract,
PmcdCost,
PmcdCustomerNum,
PmcdExpDate,
PmcdListPrice,
PmcdMpAccrual,
PmcdMpQty)
VALUES (
@AltCode,
@Contract,
@Cost,
@CustomerNum,
@ExpDate,
@ListPrice,
@MpAccrual,
@MpQty)
GO ALTER DATABASE ContractTools SET RECOVERY FULL
GO Rob Mills

---

Remove your "GO" statements

---

As soon as you have a GO, that’s the end of whatever you have been doing in the script up to that point. CREATE PROC InsertPmcdRecord
(@AltCode nvarchar(20),
@Contract nvarchar(20),
@Cost float,
@CustomerNum int,
@ExpDate datetime,
@ListPrice float,
@MpAccrual int,
@MpQty int)
AS
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON
GO … well, that’s the point where your InsertPmcdRecord procedure already ends! Check the InsertPmcdRecord procedure object that was created in the database, and you will see that the rest of the script is just not there. The ALTER DATABASE command will be executed from the QA session in which you execute the script, but it will not be a part of the definition of the stored procedure. The same for the INSERT INTO query, and the final ALTER DATABASE command. When you have a script that includes a GO, then you must be aware that this is the end of the scope for any variables that have been declared before the GO. Now I’m pretty sure that you shouldn’t be issuing ALTER DATABASE commands from a stored procedure. For one thing, and just to begin with, the database should not be in use!

---

… oooh, and check the recovery type of your database! QA will have executed the first ALTER DATABASE command, but the second one may not have been executed, since the INSERT command before it failed.

---

Thanks everyone, I’m still fairly new as you probably imagined and I really didn’t understand what GO was used for. It just seemed like all the examples I came across put that everywhere. Rob Mills

---

]]>