Reputation: 47
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
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
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:
Upvotes: 0
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
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
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