Create Proc question

[charan1976]

I first cross-posted this by mistake under ANSI_SQL forum. I apologize.
I have a simple Stored Procedure I'm trying to create as follows:
========================================
CREATE Proc CalcInventory
@ItemNo varchar
AS
CREATE VIEW Get_Qty AS
SELECT SUM(Quantity) AS TotalQty
FROM [Tech Chem Corp_$Item Ledger Entry]
WHERE [Tech Chem Corp_$Item Ledger Entry]."Item No_" = '00-0314'
GO
UPDATE [Tech Chem Corp_$Item]
SET [Tech Chem Corp_$Item]."Inventory Calc_" = (SELECT TotalQty FROM GET_QTY)
WHERE [Tech Chem Corp_$Item]."No_" = '00-0314'

drop view get_qty
GO
===========================================

I'm getting the error:
Server: Msg 156, Level 15, State 1, Procedure CalcInventory, Line 4
Incorrect syntax near the keyword 'VIEW'.

Can someone point out what I'm doing wrong here?
TIA

 

[JamesLean]

You cannot create a view inside a procedure like that, and to be honest I'm not sure why you're trying to?

CREATE Proc CalcInventory
	@ItemNo varchar
AS

DECLARE @qty int

SELECT @qty = SUM(Quantity)
FROM [Tech Chem Corp_$Item Ledger Entry]
WHERE [Item No_] = @ItemNo

UPDATE [Tech Chem Corp_$Item]
SET [Inventory Calc_] = @qty
WHERE [No_] = @ItemNo
GO

--James

 

[SQLSister]

First you would not want to create and drop a view ina stored procedure.

TRy:

UPDATE [Tech Chem Corp_$Item] 
SET [Tech Chem Corp_$Item]."Inventory Calc_" =  TotalQty
From [Tech Chem Corp_$Item] join (SELECT [Tech Chem Corp_$Item Ledger Entry]."Item No_" as ItemNo, SUM(Quantity) AS TotalQty
FROM [Tech Chem Corp_$Item Ledger Entry]
WHERE [Tech Chem Corp_$Item Ledger Entry]."Item No_" = '00-0314'
GROUP BY [Tech Chem Corp_$Item Ledger Entry]."Item No_") a
on [Tech Chem Corp_$Item]."No_" = a.ItemNo

BTW, it really is best to avoid spaces in table names and field names.

Questions about posting. See faq183-874

 

[JamesLean]

Just noticed as well, you need to specify a length for your varchar parameter. Otherwise it will default to varchar(1) and you will get unexpected results!

BTW, I have used the parameter in the queries rather than the fixed value - I assume this is what you wanted to do?

--James

 

[SQLSister]

I like James' solution better than mine.

Questions about posting. See faq183-874

 

[JamesLean]

Depending on where/how this is being used, you may want to also put the SELECT and UPDATE queries in a single transaction to maintain data integrity.

--James