Must declare the scalar variable. Though Variable Declare

Question

Hi All,

Here is the T-SQL Code that I'm trying to compile on SQL Server 2008 and getting the error. 

CREATE PROCEDURE dbo.AddNewProcurement
	(		
		@orderProductName	nvarchar(20),
		@orderVendor	nvarchar(10),
		@orderQTY		nvarchar(30),	
		@orderShippingMethod	nvarchar(60)
	)
AS
DECLARE @QTY INT
SET @QTY = CAST(@orderQTY AS INT)
DECLARE @ID INT
SET @ID = (select max(orderid)+1 from dbo.procurement)
GO
	insert into Procurement
	(orderID, orderProductName, orderVendor, orderQTY, orderShippingMethod, orderStatus, invoicePrice)
	values
	(@ID, @orderProductName, @orderVendor, @QTY, @orderShippingMethod, 'Pending', 0)
	RETURN

Now the issue is when I'm trying to create this Procedure, It created with the following error.

Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@ID".

I'm unable to understand where I'm making mistake. Altough the variable ID is already declared and initialize as well. As I'm new to T-SQL, Kindly help me is resolving this error.

Thanks for Anticipation.

Regards,

KamAbA

Answer 1

CREATE PROCEDURE dbo.AddNewProcurement
	(		
		@orderProductName	nvarchar(20),
		@orderVendor	nvarchar(10),
		@orderQTY		nvarchar(30),	
		@orderShippingMethod	nvarchar(60)
	)
AS
DECLARE @QTY INT
SET @QTY = CAST(@orderQTY AS INT)
DECLARE @ID INT
SELECT @ID = max(orderid)+1 from dbo.procurement
	insert into Procurement
	(orderID, orderProductName, orderVendor, orderQTY, orderShippingMethod, orderStatus, invoicePrice)
	values
	(@ID, @orderProductName, @orderVendor, @QTY, @orderShippingMethod, 'Pending', 0)
	RETURN

Thanks

Answer 2

Remove your GO statement. It will consider as two batches.

Try the below:

CREATE PROCEDURE dbo.AddNewProcurement
	(		
		@orderProductName	nvarchar(20),
		@orderVendor	nvarchar(10),
		@orderQTY		nvarchar(30),	
		@orderShippingMethod	nvarchar(60)
	)
AS
Begin
	DECLARE @QTY INT
	SET @QTY = CAST(@orderQTY AS INT)
	DECLARE @ID INT
	SET @ID = (select max(orderid)+1 from dbo.procurement)

	insert into Procurement
	(orderID, orderProductName, orderVendor, orderQTY, orderShippingMethod, orderStatus, invoicePrice)
	values
	(@ID, @orderProductName, @orderVendor, @QTY, @orderShippingMethod, 'Pending', 0)

END	

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

Hi Johhny and Latheesh,

Thanks to both of you. Issue resolve by removing the GO Keyword. Can u Please elaborate a little more why the GO keyword was creating the issue.

Latheesh, You said Its considering it as two batches. I'm unable to understand it. Can U Please elaborate.

Thanks for the anticiaption.

Regards,

KamAbA

Answer 4

Latheesh, You said Its considering it as two batches. I'm unable to understand it. Can U Please elaborate.

Yes, that means the @ID declared in the first batch will not be accessible in the second batch, that means, after the GO statement. (Before GO is one batch and after GO is second batch)

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

GO is not a SQL Server command.  It is a command that SSMS and many (but not all) front ends understand when you use that front end to execute SQL.  It tells the front end to send everything before the first GO to SQL, then when that set of commands has been processed, then send everything between the first GO and the second GO (or everything from the first GO to the end if there is only one GO), then everything between the second GO and the third GO, and so on until you have processed all the SQL statements.  So when you do

CREATE PROCEDURE dbo.AddNewProcurement
	(		
		@orderProductName	nvarchar(20),
		@orderVendor	nvarchar(10),
		@orderQTY		nvarchar(30),	
		@orderShippingMethod	nvarchar(60)
	)
AS
DECLARE @QTY INT
SET @QTY = CAST(@orderQTY AS INT)
DECLARE @ID INT
SET @ID = (select max(orderid)+1 from dbo.procurement)
GO
	insert into Procurement
	(orderID, orderProductName, orderVendor, orderQTY, orderShippingMethod, orderStatus, invoicePrice)
	values
	(@ID, @orderProductName, @orderVendor, @QTY, @orderShippingMethod, 'Pending', 0)
	RETURN

what happens is first SSMS sends

CREATE PROCEDURE dbo.AddNewProcurement
	(		
		@orderProductName	nvarchar(20),
		@orderVendor	nvarchar(10),
		@orderQTY		nvarchar(30),	
		@orderShippingMethod	nvarchar(60)
	)
AS
DECLARE @QTY INT
SET @QTY = CAST(@orderQTY AS INT)
DECLARE @ID INT
SET @ID = (select max(orderid)+1 from dbo.procurement)

That creates a stored procedure - not a very useful stored procedure, but it is legal.  Then when that is complete, SSMS sends

	insert into Procurement
	(orderID, orderProductName, orderVendor, orderQTY, orderShippingMethod, orderStatus, invoicePrice)
	values
	(@ID, @orderProductName, @orderVendor, @QTY, @orderShippingMethod, 'Pending', 0)
	RETURN

That, of course, gets an error since @ID is not defined.  SQL usually reports at most one error per statement or it would, of course, also give you errors for @orderProductName, @orderVendor, etc.

Tom

Answer 6

Why are you keep a quantity in a thirty character string?? this is insane; make it an integer before you invoke the procedure! 

Why is the vendor code varying length? We use DUNS everywhere on Earth for companies and business entities. It is required by law in many nations. 

Because SQL is a declarative language, we do not use local variables. The declarative languages are orthogonal. This is a fundamental principle of computer science. I assume that your novice_price is a DECIMAL(s,p), so why do you insert an INTEGER zero? 

CREATE PROCEDURE Add_New_Procurement
(@in_order_product_name NVARCHAR(20),
 @in_order_vendor_duns CHAR(9), --- industry standard
 @in_order_qty INTEGER, -- rational decision
 @in_order_shipping_method CHAR(5)) – needs encoding!
AS
INSERT INTO Procurement
 (order_id, order_product_name, order_vendor_duns, order_qty, order_shipping_method, order_status, invoice_price)
 VALUES
 ((SELECT MAX(order_id)+1 FROM DBO.Procurement),
-- NEXT VALUE FOR Procurement_Orders, 
 @in_order_product_name, @in_order_vendor, @in_order_qty, @in_order_shipping_method, 'pending', 0.00);

Now throw out the scalar subquery. Use the CREATE SEQUENCE statement. 

CREATE SEQUENCE Procurement_Orders
AS INTEGER 
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 NO MAXVALUE
 NO CYCLE.

This was not too bad; you can be writing good SQL in one or two years :)

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 7

This was not too bad; you can be writing good SQL in one or two years :) - Celko

That's pretty much the equivalent of a top class certification in ANSI SQL ;)

I would be framing that comment if I was the OP - and applying for a job at NASA or one of those companies where you get to program things that make a huge noise and fly through the air.

---

Thanks! Josh