Given below is an example on how to use table types. Table types can be used to pass data array data from code to SQL stored procedure instead of passing the data in CSV or XML format. The example also shows how to batch SQL update statements.
CREATE TYPE [dbo].[BIGIntArrayTemp] AS TABLE
(
[Val] [bigint] NULL
)
GO
DECLARE @tableType [dbo].[BIGIntArrayTemp]
insert into @tableType (val) values (1)
insert into @tableType (val) values (2)
insert into @tableType (val) values (4)
insert into @tableType (val) values (8)
insert into @tableType (val) values (7)
insert into @tableType (val) values (4)
insert into @tableType (val) values (6)
DECLARE @records TABLE(records_id INT IDENTITY(1,1),SomeId BIGINT)
DECLARE @toprecords TABLE(SomeId BIGINT)
DECLARE @tmp TABLE(SomeId BIGINT,flag bit)
insert into @tmp(SomeId) values (1)
insert into @tmp(SomeId) values (2)
insert into @tmp(SomeId) values (4)
insert into @tmp(SomeId) values (8)
insert into @tmp(SomeId) values (7)
insert into @tmp(SomeId) values (4)
insert into @tmp(SomeId) values (6)
insert into @tmp(SomeId) values (10)
INSERT INTO @records (SomeId)
SELECT SomeId
FROM @tmp t
INNER JOIN @tableType ids ON ids.Val = t.SomeId
DECLARE @count INT
DECLARE @batch_size INT
SET @batch_size = 100 --set batch size
INSERT INTO @toprecords (SomeId)
SELECT top (@batch_size) SomeId FROM @records ORDER BY records_id desc
SELECT @count = COUNT(1) FROM @toprecords
WHILE (@count > 0)
BEGIN
BEGIN TRY
BEGIN TRANSACTION --Begin transaction for each
batch
UPDATE
g
SET g.flag=1
FROM @tmp g
INNER
JOIN @toprecords t
ON t.SomeId = g.SomeId
--
clean toprecords table variable
DELETE FROM @toprecords
--
remove the top records
DELETE FROM dd
FROM
(
SELECT
top (@batch_size) * FROM @records ORDER BY records_id desc
) dd
--
select next top records
INSERT INTO @toprecords (SomeId)
SELECT
top (@batch_size) SomeId FROM @records ORDER BY records_id desc
SELECT @count = count(1) FROM @toprecords
COMMIT TRANSACTION -- commit transaction for
each batch
END TRY
BEGIN CATCH
ROLLBACK
TRANSACTION -- rollback if any error
occurs
END CATCH
END
SELECT * FROM @tmp
------------------------------
Another way to implement it without try...catch and transaction
--------------------------------
DECLARE @tableType [dbo].[BIGIntArrayTemp]
insert into @tableType (val) values (1)
insert into @tableType (val) values (2)
insert into @tableType (val) values (4)
insert into @tableType (val) values (8)
insert into @tableType (val) values (7)
insert into @tableType (val) values (4)
insert into @tableType (val) values (6)
DECLARE @records TABLE(records_id INT IDENTITY(1,1),SomeId BIGINT)
DECLARE @updatelist TABLE(SomeId BIGINT)
DECLARE @tmp TABLE(SomeId BIGINT,flag bit)
insert into @tmp(SomeId) values (1)
insert into @tmp(SomeId) values (2)
insert into @tmp(SomeId) values (4)
insert into @tmp(SomeId) values (8)
insert into @tmp(SomeId) values (7)
insert into @tmp(SomeId) values (4)
insert into @tmp(SomeId) values (6)
insert into @tmp(SomeId) values (10)
DECLARE @count INT
DECLARE @batch_size INT,@RecordCount INT
SET @batch_size = 2 --set batch size
INSERT INTO @records (SomeId)
SELECT SomeId
FROM @tmp t
INNER JOIN @tableType ids ON ids.Val = t.SomeId
Select @RecordCount = @@RowCount --initialize RecordCount
WHILE (@RecordCount > 0)
BEGIN
Delete top(@batch_size) r
Output deleted.SomeId Into @updatelist
From @records r
Select @RecordCount = @@RowCount --update record count
variable
PRINT
('Record Count --'+
convert(varchar,@RecordCount))
UPDATE g
SET
g.flag=1
FROM
@tmp g
INNER
JOIN @updatelist t
ON
t.SomeId = g.SomeId
-- clean toprecords table variable
Delete from @updatelist
Waitfor Delay '00:00:00.5'
END
SELECT * FROM @tmp
Table type data can be used in stored procedure to pass array data.
CREATE PROCEDURE [dbo].[xyz]
@ids dbo.[BIGIntArrayTemp] READONLY
AS
BEGIN
--
Proc body
END
