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: stackoverflow.com/questions/54418797/… — Thom A
– Thom A ♦, Commented Jan 29, 2019 at 10:37

Salman Arshad · Accepted Answer · 2019-01-29 10:35:54Z

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, 2019 at 10:35

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S.Jose · Accepted Answer · 2019-01-29 10:40:05Z

0

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)

answered Jan 29, 2019 at 10:40

S.Jose

2461 silver badge7 bronze badges

Comments

Eliseu Marcos · Accepted Answer · 2019-01-29 10:58:14Z

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

Collectives™ on Stack Overflow

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 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related