July 3, 2009 at 7:48 am
I'm creating a stored procedure and using a declared variable in the Northwind database.
Do I need to include the GO statement after declaring the variable, and for that matter, do I need to have the statement for the USE command?
CREATE PROCEDURE up_Fill_Product_Grid
/********************************************************
SP: up_Fill_Product_Grid
Takes a SuplierID from the Supplier table and returns the
ProductID, ProductName, UnitPrice, quantity on hand,
reorder point, and quantity on order for all products
offered by that supplier.
Created by: Chuck Selig
Last modified: 2 July 2009
********************************************************/
@SupplierID int
AS
USE Northwind
DECLARE @OrderAmt SmallInt
SET @OrderAmt = 0
SELECT ProductID, ProductName, UnitPrice, UnitsInStock,
ReorderLevel, UnitsOnOrder, @OrderAmt As OrderAmt
FROM Products
INNER JOIN Suppliers
ON Products.ProductID = Suppliers.ProductID
WHERE @SupplierID = SupplierID
July 3, 2009 at 8:12 am
Do I need to include the GO statement after declaring the variable
GO isn't actually a TSQL statement... it is used by the likes of SSMS etc to signify the end of a batch of TSQL statements. Variables go out of scope at the end of a batch, so if you include a GO statement, your variable will no longer exist.
In your case, as you are trying to do this inside a stored procedure, the GO statement will signify the end of the procedure, and any statements following this will be treated as another batch, and executed... which can be a bit of a problem if the next statement is a DELETE!!!
and for that matter, do I need to have the statement for the USE command?
You can't use the USE statement inside a stored procedure (or function or trigger).
If you want to reference an object in a different database inside your procedure, you will have to use the notation dbname.owner.object e.g. SELECT * FROM northwind.dbo.suppliers.
EDIT. Added the clarification below.
If you want your procedure to be run in the context of the Northwind database, then put the USE Northwind statement before your CREATE PROCEDURE. The procedure will then be stored in and executed in the context of the Northwind database, and you won't need to use the dbname.owner.object notation.
July 3, 2009 at 8:46 am
Thanks for the advice.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply