June 9, 2017 at 9:25 am
I have a stored procedure where I am trying to update stock values in a table based on values in a table variable. After updating the values, I want to capture the before and after values and store in another table variable. I am getting a syntax error - please can you help pin-point the reason this is generating a syntax error? I am using SSMS 2014
1) First I declare my table variable (this bit works):
DECLARE @StockAdjustmentDetails TABLE (OrderID BIGINT NOT NULL
,OrderREF BIGINT NOT NULL
,OrderLineID BIGINT NOT NULL
,LineQuantity INT NOT NULL
,ProductID BIGINT NOT NULL
,LocationID BIGINT NOT NULL
,ActivityType BIGINT NOT NULL
,StockAuditTypeID INT NOT NULL
,ProductCode NVARCHAR(255)
,LocationRank INT NOT NULL)
2) Next I insert data in to the table variable @StockAdjustmentDetails from a derived table) (this bit works):
;WITH E AS (
SELECT dbo.PP_tbl_Order.fldOrderID
,dbo.PP_tbl_Order.fldOrderREF
,dbo.PP_tbl_OrderLine.fldOrderLineID
,dbo.PP_tbl_OrderLine.fldQuantity
,.fldProductID
,dbo.tlkp_Location.fldLocationID
,@ActivityTypeID AS [ActivityTypeID]
,@StockAuditTypeID AS [StockAuditTypeID]
,.fldProductCode
,ROW_NUMBER() OVER(PARTITION BY .fldProductID ORDER BY dbo.tlkp_Location.fldQuantity DESC) AS [LocationRank]
FROM dbo.PP_tbl_Order INNER JOIN
dbo.PP_tbl_OrderLine ON dbo.PP_tbl_Order.fldOrderID = dbo.PP_tbl_OrderLine.fldOrderID INNER JOIN
dbo.tlkp_Product AS ON dbo.PP_tbl_OrderLine.fldProductID = .fldProductID LEFT OUTER JOIN
dbo.tlkp_Location ON tlkp_Location.fldProductID = .fldProductID
WHERE (dbo.PP_tbl_Order.fldOrderID = 1) AND dbo.tlkp_Location.fldEnabled = 1
)
INSERT INTO @StockAdjustmentDetails (OrderID,OrderREF,OrderLineID,LineQuantity,ProductID,LocationID,ActivityType,StockAuditTypeID,ProductCode,LocationRank)
SELECT E1.fldOrderID
,E1.fldOrderREF
,E1.fldOrderLineID
,E1.fldQuantity
,E1.fldProductID
,E1.fldLocationID
,E1.ActivityTypeID
,E1.StockAuditTypeID
,E1.fldProductCode
,E1.LocationRank
FROM E AS E1
WHERE E1.LocationRank=1
3) Next I declare a table variable to write the before and after values to (this bit works):
DECLARE @temp_audit TABLE (LocationID BIGINT NOT NULL, QuantityBefore INT, QuantityAfter INT)
4) Now I use the values in the table variable @StockAdjustmentDetails to update the stock values in a real table, and save the before and after values to the table variable @temp_audit (The code never runs because this bit generates the error "Incorrect syntax near OUTPUT" but I'm not sure what is wrong.)
DECLARE @temp_audit TABLE (LocationID BIGINT NOT NULL, QuantityBefore INT, QuantityAfter INT)
UPDATE tlkp_Location
SET tlkp_Location.fldQuantity = tlkp_Location.fldQuantity - @StockAdjustmentDetails.fldQuantity
FROM tlkp_Location INNER JOIN @StockAdjustmentDetails ON tlkp_Location.fldLocationID = @StockAdjustmentDetails.fldLocationID
OUTPUT inserted.fldLocationID, deleted.fldQuantity, inserted.fldQuantity INTO @temp_audit
I have tried to highlight some of the code in yellow which is getting the red squiggle underline in SSMS indicating something is wrong.
Thanks in advace
Rob
June 9, 2017 at 9:35 am
Check this out: https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql
You will notice you have the OUTPUT clause in the wrong location in your UPDATE statement. If there are other issues, not enough information to really help.
June 9, 2017 at 9:40 am
Check the placement of your OUTPUT clause, and compare to https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql.
OUTPUT goes after SET and before FROM.
Cheers!
EDIT: Darn, beaten to it yet again. I'm beginning to think there's some conspiracy; I even refreshed the page before posting to see if anyone had already posted. C'est la vie 🙂
June 9, 2017 at 9:55 am
Lynn Pettis and Jacob Wilkins - whoops I think I skimmed that too fast the first time and misread option as output, thank you for pointing that out!
Unfortunately my code above still doesn't work, I have changed the last bit of the stored procedure to:
UPDATE tlkp_Location
SET tlkp_Location.fldQuantity = tlkp_Location.fldQuantity - @StockAdjustmentDetails.fldQuantity
OUTPUT inserted.fldLocationID, deleted.fldQuantity, inserted.fldQuantity INTO @temp_audit
FROM tlkp_Location INNER JOIN @StockAdjustmentDetails ON tlkp_Location.fldLocationID = @StockAdjustmentDetails.fldLocationID
Now SSMS is producing the error "Must declare the scalar variable @StockAdjustmentDetails".
Lynn - you said that there wasn't enough information to help further - I'm not sure what else to provide as this is all there is in my stored procedure - is there something specific I've missed?
Thanks, Rob
June 9, 2017 at 9:59 am
r.gall - Friday, June 9, 2017 9:55 AMLynn Pettis and Jacob Wilkins - whoops I think I skimmed that too fast the first time and misread option as output, thank you for pointing that out!
Unfortunately my code above still doesn't work, I have changed the last bit of the stored procedure to:
UPDATE tlkp_Location
SET tlkp_Location.fldQuantity = tlkp_Location.fldQuantity - @StockAdjustmentDetails.fldQuantity
OUTPUT inserted.fldLocationID, deleted.fldQuantity, inserted.fldQuantity INTO @temp_audit
FROM tlkp_Location INNER JOIN @StockAdjustmentDetails ON tlkp_Location.fldLocationID = @StockAdjustmentDetails.fldLocationIDNow SSMS is producing the error "Must declare the scalar variable @StockAdjustmentDetails".
Lynn - you said that there wasn't enough information to help further - I'm not sure what else to provide as this is all there is in my stored procedure - is there something specific I've missed?
Thanks, Rob
Well, we can't see what you see. Could you answer your question based only on what is in this post?
As for the error above, it tells you exactly what is needed, to declare the variable. Now, if that is done elsewhere in your code we can't see that from here.
June 9, 2017 at 10:00 am
You need to alias the table variable to reference it outside FROM (e.g., column references in the JOIN criteria).
Check the General Remarks section at https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql
If you don't do that, it'll give you the bogus error about needing to declare a scalar variable.
Cheers!
EDIT: Clarified some wording I wasn't thrilled with.
June 12, 2017 at 1:47 am
Jacob Wilkins - Friday, June 9, 2017 10:00 AMYou need to alias the table variable to reference it outside FROM (e.g., column references in the JOIN criteria).Check the General Remarks section at https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql
If you don't do that, it'll give you the bogus error about needing to declare a scalar variable.
Cheers!
EDIT: Clarified some wording I wasn't thrilled with.
Thank you Jacob - I marked Lynn Pettis as the answer because they got there first but I do appreciate your additional comments. Apologies both for going off tangent to the original problem!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply