SQL Pass multiple IDs as one variable

sknav 1 Reputation point
2021-03-16T20:11:59.913+00:00

I'm looking to pass a theoretically unlimited number of IDs as one variable to update a table. Please help with the code.

declare @IDS_test nvarchar(max)
set @IDS_test = (select SS.[Id]
from stockmanager.Stock SS
inner join stockmanager.StockStatus SSS on SS.StockStatusId=SSS.Id
inner join stockmanager.StockStore SST on SS.Id=SST.StockId
inner join storedatabase.Store SDS on SST.StoreId=SDS.Id
where SSS.Id=2 and SST.StoreId=124)

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,482 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,831 Reputation points
    2021-03-16T20:27:51.587+00:00

    @sknav ,

    STRING_AGG() function could be handy for your scenario. It will work starting from SQL Server 2017 onwards.

    Check it out here: string-agg-transact-sql

    Along the following:

    declare @IDs nvarchar(max);  
    
    select @IDs = STRING_AGG(CAST(SS.[Id] AS VARCHAR(10)), ',')  
    from stockmanager.Stock SS  
    inner join stockmanager.StockStatus SSS on SS.StockStatusId=SSS.Id  
    inner join stockmanager.StockStore SST on SS.Id=SST.StockId  
    inner join storedatabase.Store SDS on SST.StoreId=SDS.Id  
    where SSS.Id=2 and SST.StoreId=124;  
    

  2. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-16T21:12:42.217+00:00

    If you are going to use those IDs to update a table, what use to you have of a string?

    This seems more natural:

    INSERT @Ids(id)
    select SS.[Id]
    from stockmanager.Stock SS
    inner join stockmanager.StockStatus SSS on SS.StockStatusId=SSS.Id
    inner join stockmanager.StockStore SST on SS.Id=SST.StockId
    inner join storedatabase.Store SDS on SST.StoreId=SDS.Id
    where SSS.Id=2 and SST.StoreId=124
    
    UPDATE tbl
    SET     ...
    WHERE ids IN (SELECT id FROM @ids)
    

    If you really want a comma separated list you can to:

    SET @IDs = 
     (select concat(SS.[Id], ',')
    from stockmanager.Stock SS
    inner join stockmanager.StockStatus SSS on SS.StockStatusId=SSS.Id
    inner join stockmanager.StockStore SST on SS.Id=SST.StockId
    inner join storedatabase.Store SDS on SST.StoreId=SDS.Id
    where SSS.Id=2 and SST.StoreId=124
    FOR XML PATH(''))
    SELECT @IDs = substring(@Ids, 1, len(@IDs) - 1)
    
    0 comments No comments

  3. EchoLiu-MSFT 14,571 Reputation points
    2021-03-17T06:03:37.28+00:00

    Hi @sknav

    Welcome to the microsoft TSQL Q&A forum!

    I see that you have accepted the answer in another forum.

    If you want to post the same problem in multiple forums in the future, I suggest you provide us with a link to the same problem, so that if the problem has been solved in a forum, we can save time and not research.

    Regards
    Echo

    0 comments No comments