Multi-Value Parameter in Microsoft SQL Server 2008

Question

I am trying to pass multiple values to a parameter default in SQL Server 2008.

Here is what I have:

Declare @PInactive  Int

Set @PInactive = Case When @PInactive is null Then (0 , 1) Else @PInactive End

Select
    ClientID
   ,PInactive
From
   #Client
Where
   PInactive in (@PInactive)

Gives me an error on Then (0 , 1)- stating incorrect syntax. I also tried with single quotes around the 0 and 1 to no avail.

Any suggestions would be greatly appreciated. I have many of these I need to use in this query.

Thanks, Scott

Jaaz Cole · Accepted Answer · 2014-07-17 23:29:03Z

1

You can't have multi-value int variables. You can, however, have a table variable of ints.

DECLARE @PInactive TABLE (State INT);
IF NOT EXISTS (SELECT 1 FROM @PInactive) INSERT INTO @PInactive (State) VALUES (0),(1);

Select
ClientID
,PInactive
From
#Client
Where
PInactive in (SELECT State FROM @PInactive)

answered Jul 17, 2014 at 23:29

Jaaz Cole

3,1981 gold badge17 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Multi-Value Parameter in Microsoft SQL Server 2008

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related