What's the most efficient way to declare multiple values for a parameter?-CodePudding

I'm running a batch update script where @masterNameID in (21,22,23) but I'm getting syntax errors when I run this snippet;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@MasterNameID AS INT in (21,22,23)
,@RowCount AS INT

I've tried declaring a temp table by using the below but still can't get it to run;

Declare @MasterNameTlb table (MasterNameID INT (5))
 Insert into @MasterNameTlb (MasterNameID) values (21,22,23)
  Set @MasterNameID AS INT = select MasterNameID from @MasterNameTlb

Any ideas on how to get this to run or if there's a more efficient way to declare multiple values for an update script?

CodePudding user response：

It looks like you are trying to insert multiple values into a table variable. The correct syntax is:

Declare @MasterNameTlb table (MasterNameID INT);  -- use primary key?
Insert into @MasterNameTlb (MasterNameID) values (21),(22),(23);

select MasterNameID
from @MasterNameTlb AS t;

It's unclear exactly what you want to do with this table, but you can join it like any other.

CodePudding user response：

Try these:

DROP TABLE IF EXISTS [#MasterName];
GO
-- Example 1 into a TEMP table
Select [MasterName].[ID] INTO [#MasterName] FROM ( VALUES (21),(22),(23) )[MasterName]([ID]);
Select [TBL]= '[#MasterName]', * from [#MasterName];
-- Example 2 into a DECLARE table
Declare @MasterName TABLE ([ID] INT);
INSERT INTO @MasterName([ID])
Select [MasterName].[ID] FROM ( VALUES (21),(22),(23) )[MasterName]([ID]);
Select [TBL]= '@MasterName', * from @MasterName;

CodePudding user response：

Are you trying to update some other table, not shown here, where a value is in (21,22,23)? Your temp table idea could work. After you create the temp table, you would do the update like this:

update mytable
set somecolumn = somevalue 
where somekey in (
    select * from @MasterNameTlb
);

If that's not what you trying to do, maybe add more detail to the question.