How do I create a variable/parameter that is a string of values in SQL SSMS that I can use as a substitute in my where clause?

Question

This may be a very basic question, but I have been struggling with this.

I have a SSMS query that I'll be using multiple times for a large set of client Ids. Its quite cumbersome to have to amend the parameters in all the where clauses every time I want to run it.

For simplicity, I want to convert a query like the one below:

SELECT
ID,
Description

From TestDb

Where ID in ('1-234908','1-345678','1-12345')

to a query of the format below so that I only need to change my variable field once and it can be applied across my query:

USE TestDb

DECLARE @ixns NVARCHAR(100)

SET @ixns = '''1-234908'',''1-345678'',''1-12345'''

SELECT 
ID,
Description

From TestDb

Where ID IN @ixns

However, the above format doesn't work. Can anyone help me on how I can use a varchar/string variable in my "where" clause for my query so that I can query multiple IDs at the same time and only have to adjust/set my variable once?

Thanks in advance :D

This question was literally jsut asked by someone else: https://stackoverflow.com/questions/54418797/sql-server-using-in-keyword-pass-string-array-query/54418992?noredirect=1#54418992 — Thom A, Jan 29 '19 at 10:37

score 1 · Accepted Answer · answered Jan 29 '19 at 10:35

1

The most appropriate solution would be to use a table variable:

DECLARE @ixns TABLE (id NVARCHAR(100));
INSERT INTO @ixns(id) VALUES
('1-234908'),
('1-345678'),
('1-12345');

SELECT ID, Description
FROM TestDb
WHERE ID IN (SELECT id FROM @ixns);

answered Jan 29 '19 at 10:35

Salman A

262,204
82
430
521

score 0 · Answer 2 · answered Jan 29 '19 at 10:40

You can load ids to temp table use that in where condition

USE TestDb

DECLARE @tmpIDs TABLE
    (     
      id VARCHAR(50)     
    )

insert into @tmpIDs values ('1-234908')
insert into @tmpIDs values ('1-345678')
insert into @tmpIDs values ('1-12345')



SELECT 
ID,
Description
From TestDb
Where ID IN (select id from @tmpIDs)

score 0 · Answer 3 · answered Jan 29 '19 at 10:58

The most appropriate way is to create a table type because it is possible to pass this type as parameters.

1) Creating the table type with the ID column.

    create type MyListID as table 
    (
        Id int not null
    )
    go

2) Creating the procedure that receives this type as a parameter.

    create procedure MyProcedure
                        (
                            @MyListID as MyListID readonly
                        )
    as

        select 
            column1,
            column2
            ...
        from
            MyTable
        where
            Id in (select Id from @MyListID)

3) In this example you can see how to fill this type through your application ..: https://stackoverflow.com/a/25871046/8286724

How do I create a variable/parameter that is a string of values in SQL SSMS that I can use as a substitute in my where clause?

3 Answers3