I'm developing this stored procedure on SQL Server 2012.
The stored procedure will update Quantity rows in EXTERNAL_CODES table for each row in @newBatches parameter. It's like a loop, I will need to create a new row in BATCHES table for each row in @newBatches parameter.
And then, I have to update Quantity rows in EXTERNAL_CODES table with each batchId created.
CREATE PROCEDURE [dbo].[CreateBatchAndKeepExternalCodes]
@newBatches as dbo.CreateBatchList READONLY,
@productId int
AS
set nocount on;
declare @lowestCodeLevel tinyint;
-- ======== VALIDATION ==========
if ((select count(name) from @newBatches) = 0)
return -112;
-- ====== CODE ========
-- Get lowest aggregation level.
set @lowestCodeLevel =
(select min(c.application_code)
from CHINA_CODES_HEADER c, PRODUCTS p
where p.Id = @productId and c.DRUG_TEN_SEATS = p.PRODUCT_CODE);
begin transaction;
insert into BATCHES (PRODUCT_ID, NAME, CREATED)
select @productId, Name, CAST(SYSDATETIMEOFFSET() as nvarchar(50))
from @newBatches;
update top(t.Quantity) EXTERNAL_CODES
set BATCH_ID = (select ID from BATCHES where NAME = t.Name)
, USED = 1
from (select Name, Quantity from @newBatches) t
where PRODUCT_ID = @productId and CODE_LEVEL = @lowestCodeLevel;
commit transaction;
RETURN 0
I get an error on this update:
update top(t.Quantity) EXTERNAL_CODES
set BATCH_ID = (select ID from BATCHES where NAME = t.Name)
, USED = 1
from (select Name, Quantity from @newBatches) t
where PRODUCT_ID = @productId and CODE_LEVEL = @lowestCodeLevel;
The error is here: update top(t.Quantity). It can't find t.Quantity.
dbo.CreateBatchList is:
CREATE TYPE [dbo].[CreateBatchList] AS TABLE
(
Name nVARCHAR(20),
Quantity int
)
My problem is that I can't set to update Quantity rows. Any idea?
The error (or warning) message is:
SQL71005: The reference to the column t.Quantity could not be resolved.
Maybe I could use MERGE.
1 Answer 1
Your update statement is quite confusing. If for instance @newBatches table has multiple lines, then you are saying, pick all the Quantity from @newBatches in Top?
Anyway, I think the solution is to use a loop to use each line from @newBatches to update. I have modified your code in order to test it on my side, and have replaced all the tables with Table Variables. You may find it helpful.
But still without any Order By clause and without knowing actual business logic, I can't say this solution is correct.
DECLARE @productID int;
DECLARE @lowestCodeLevel int;
DECLARE @EXTERNAL_CODES table(BATCH_ID varchar(100), USED bit, PRODUCT_ID int, CODE_LEVEL int);
DECLARE @BATCHES table(ID int, NAME varchar(100));
DECLARE @newBatches table(Name nVARCHAR(20), Quantity int);
-- we don't know at this point whether @newBatches has some column
-- through which we can uniquely identify a row
-- that is why we are creating this new table in which we have Row_ID column
-- through which we can extract each line
DECLARE @newBatchesWithRowID table(Row_ID int not null identity, Name nVarchar(20), Quantity int);
INSERT INTO @newBatchesWithRowID(Name, Quantity)
SELECT Name, Quantity
FROM @newBatches;
DECLARE @prvRow_ID int;
-- loop to iterate in @newBatchesWithRowID table
WHILE(1 = 1)
Begin
DECLARE @row_ID int = NULL;
DECLARE @Name varchar(100);
DECLARE @Quantity int;
SELECT TOP 1 @row_ID = Row_ID
, @Quantity = Quantity
, @Name = Name
FROM @newBatchesWithRowID
WHERE Row_ID > @prvRow_ID OR @prvRow_ID IS NULL
ORDER BY Row_ID;
If @row_ID IS NULL Break;
SET @prvRow_ID = @row_ID;
update top(@Quantity) @EXTERNAL_CODES
set BATCH_ID = (select ID from @BATCHES where NAME = @Name)
, USED = 1
where PRODUCT_ID = @productId and CODE_LEVEL = @lowestCodeLevel;
END
t.Quantityis potentially different for each row in the from clause. besides, there is no meaning totopwithoutorder by.top(number)should be a number that is decided before the update statement...