question

sknav-5025 avatar image
0 Votes"
sknav-5025 asked EchoLiu-msft action

SQL Pass multiple IDs as one variable


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

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Hi Sara,

It seems that you are mushrooming your question: sql-pass-multiple-ids-to-a-single-parameter all over the place.


0 Votes 0 ·

She wouldn't be the first to do so. But, yes, it is a bit of a nuisance. People here are spending their free time answering questions. Kid of waste of time if the question has been answered elsewhere.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

@sknav-5025,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your reply, but i have sql server 2016 version. Unfortunately it does'nt support STRING_AGG

0 Votes 0 ·

I added a comment to your question.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered sknav-5025 edited

Hi @sknav-5025

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.