CODEWITHSUNDEEP
sqlsql-serverparameters
Yemisi Adeoluwa
Yemisi Adeoluwa

Reputation: 47

How do I use an IN Clause in Sql Server Parameter

Can someone help me please, I am trying to use an IN clause in a parameter in SQL server 2018. I would then use the parameter in the Where clause within the select statement This is the query below:

CREATE STORE PROC EMPLOYEE_INFO
AS
BEGIN
DECLARE @City varchar(100)
        @Contract_type varchar(100)

SET     @City = 'London, Glasgow, Manchester'
       ,@Contract_type = 'Permanent, Temporary, Fixed Term'

Select EmpID, Emp_Start_Date, Address, City, Contract_type
from Employee
Where City NOT IN ('@City')
AND Contract_Type IN ('Permanent', 'Temporary', 'Fixed Term')

END

Upvotes: 0

Views: 130

Answers (5)

Cetin Basoz
Cetin Basoz

Reputation: 23867

@city parameter is meant to be passed multiple city names separated with a comma. Then you check to see if field City is contained in @city like this:

CREATE STORE PROC EMPLOYEE_INFO
AS
BEGIN
DECLARE @City varchar(100)
        @Contract_type varchar(100);

SET     @City = 'London, Glasgow, Manchester'
       ,@Contract_type = 'Permanent, Temporary, Fixed Term';


SET @City = ',' + @City + ','; -- ensure starts and ends with comma or space


Select EmpID, Emp_Start_Date, Address, City, Contract_type
from Employee
Where @City NOT LIKE '%[, ]'+City+'[ ,]%'
AND Contract_Type IN ('Permanent', 'Temporary', 'Fixed Term');

END

This would work even in old versions too (but wouldn't utilize an index).

Upvotes: 0

Reza Jenabi
Reza Jenabi

Reputation: 4319

You can use the following code

DECLARE @City varchar(100)
DECLARE       @Contract_type varchar(100)

SET    @City = '''London'', ''Glasgow'',''Manchester'''
Set    @Contract_type = '''Permanent'', ''Temporary'',''Fixed Term'''

EXEC (
    'Select EmpID, Emp_Start_Date, Address, City, Contract_type
    FROM Employee
    Where City NOT IN ('+@City+')
    AND Contract_Type IN ('+@Contract_type+')')

Result:

enter image description here

Upvotes: 0

LukStorms
LukStorms

Reputation: 29677

How about making the @City a table variable?

declare @City table (
 name varchar(100) not null primary key
);

insert into @City (name) values 
('London'), ('Glasgow'), ('Manchester');

Then use it in the IN

...
WHERE City NOT IN (SELECT name FROM @City)
...

And if you do want to keep @City as a varchar?
Then you can just fill a table variable by using STRING_SPLIT

DECLARE @City VARCHAR(100);
SET @City = 'London, Glasgow, Manchester';

DECLARE @Cities TABLE (
 NAME VARCHAR(100) NOT NULL PRIMARY KEY
);

INSERT INTO @Cities (name)
  SELECT DISTINCT LTRIM(value) 
  FROM STRING_SPLIT(@City, ',') s;

Then:

...
WHERE City NOT IN (SELECT name FROM @Cities)
...

Upvotes: 3

Kgeorge
Kgeorge

Reputation: 136

Assuming you meant that you are using SQL 2019 and that 2018 was a typo

The simplest way to do this is to use STRING_SPLIT() and to use a delimiter that is not in your data, the example below is based on the data being comma-separated. 

DECLARE
    @City          VARCHAR(100)
  , @Contract_type VARCHAR(100) ;

SET @City = 'London, Glasgow, Manchester' ;
SET @Contract_type = 'Permanent, Temporary, Fixed Term' ;


SELECT
    EmpID
  , Emp_Start_Date
  , Address
  , City
  , Contract_type
FROM Employee
WHERE
    City NOT IN ( 
        SELECT TRIM([value]) FROM STRING_SPLIT(@City,',')

    ) 
    AND Contract_Type IN ( 
        SELECT TRIM([value]) FROM STRING_SPLIT(@Contract_type,',')
    ) ;

Note: The use of TRIM is to remove spaces around your data

Upvotes: 0

George Joseph
George Joseph

Reputation: 5932

Here is one way to do this. You if you are on SQL 2017, you can use STRING_SPLIT function to convert the comma seperated values as rows and use them in the NOT IN clause.

Otherwise you have this option

DECLARE @City varchar(100)
        @Contract_type varchar(100)

SET     @City = 'London, Glasgow, Manchester'
       ,@Contract_type = 'Permanent, Temporary, Fixed Term'



    ;with comma_to_rows
      as (SELECT Split.a.value('.', 'VARCHAR(100)') AS Data  
           FROM  
                (SELECT CAST ('<M>' + REPLACE(@City, ',', '</M><M>') + '</M>' AS XML) AS Data                   
                ) AS A 
           CROSS APPLY Data.nodes ('/M') AS Split(a)  
           )
    Select EmpID
         , Emp_Start_Date
         , Address
         , City
         , Contract_type
     from Employee
    Where City NOT IN (select data from comma_to_rows)
      AND Contract_Type IN ('Permanent', 'Temporary', 'Fixed Term')

Upvotes: 0

Related Questions