SQL Server variable datetime with array

Question

Can we define a variable of type array in SQL Server?

I have a table and this pk type is datetime.

CREATE TABLE [dbo].[tblTest]
(
 [TestDate] [datetime] NOT NULL, -- this column is the primary key
 [UserId] [int] NOT NULL,
 [RotationType] [int] NOT NULL
)

And I need a query to update userId

UPDATE TABLE dbo.tblTest
SET RotationType = 1
WHERE TestDate IN (@testDates)

How can I declare this @testDates variable?

• Alex K.: TABLE variables: learn.microsoft.com/en-us/sql/t-sql/data-types/…
• Alejandro: Does this answer your question? Array var in SQL Server 2008
• marc_s: There's no TABLE in the UPDATE statement - just use UPDATE dbo.tblTest SET ....

Answer 1

you can use table variables:

declare @tblvar as table (TestDate datetime)
insert into @tblvar values (('2021-09-01'),...);
UPDATE TABLE dbo.tblTest
SET RotationType = 1
WHERE TestDate IN (select TestDate from @tblvar)

or you can declare it as type so you can pass it to procs:

declaration phase :

create type DateTimeArray as table (TestDate datetime);
CREATE PROCEDURE funcname (@inputDates DateTimeArray)
as
UPDATE TABLE dbo.tblTest
SET RotationType = 1
WHERE TestDate IN (select TestDate from @inputDates)
end 

calling:

declare @tblvar as DateTimeArray;
insert into @tblvar values (('2021-09-01'),...);
exec funcname @tblvar

• Amazon: i will use this variables in sp, so i need pass this parameter how can i it ?
• eshirvana: see my updated asnwer , didn't test , so it might have some minor syntax issue , but you got the idea ;)