SQL Pass multiple IDs to a single parameter

Question

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 nvarchar(max)

set @IDs = (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

Does this answer your question? [Pass test data to table-valued parameter within SQL](https://stackoverflow.com/questions/43142575/pass-test-data-to-table-valued-parameter-within-sql) — Charlieface, Mar 16 '21 at 20:30

Stu · Answer 1 · 2021-03-16T21:05:27.550

You're probably better using a custom table-valued parameter, it means you don't have to concatenate into a string and then split it out again to use the values.

First create your custom type

create type [dbo].IdList as table([Value] int not null)
go

Where you get the values, insert into the custom type

declare @IDs IdList
insert into @IDs([Value])
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

/* Pass to another procedure*/

exec MyProc @IdList=@IDs
go

And use them in another procedure

create procedure MyProc
@IdList IdList
as
/*Do stuff with list if IDs*/
select * from @IdList

go

Oh and using a TVP in this manner means you can also pass an object/array of values directly from something like a .net app, which is pretty cool.

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-16T20:54:03.757

If you are using at least SQL Server 2017 and you want to have those ids as comma separated string then try this:

declare @IDs nvarchar(max)
set @IDs= (select string_agg(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 SQL Server older than 2017 you can use STUFF() with FOR XML PATH()

  SET  @IDs= STUFF((SELECT ',' + QUOTENAME(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(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

If you want only distinct IDs then :

SET  @IDs= STUFF((SELECT Distinct ',' + QUOTENAME(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(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

Thank you for your reply, but i have sql server 2016 version. Unfortunately it does'nt support STRING_AGG. — Sara, Mar 16 '21 at 20:50
you can use stuff with for xml path. I will update my answer. — Kazi Mohammad Ali Nur Romel, Mar 16 '21 at 20:52
Seriously, you don't want to start down the path of id concatenation... there are so many questions on here from people dealing with the fallout of that decision further down the line. Use a table valued parameter. — Dale K, Mar 16 '21 at 20:57

SQL Pass multiple IDs to a single parameter

2 Answers2