Dynamic 'In' Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic 'In' Clause

Author

Topic

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2001-04-06 : 15:51:37

I have been searching for weeks on end for an answer to this problem. No one has been able to solve it.

I want to send a comma delimited list to a Stored Procedure for use in an 'In' clause.

Here is the table I want to query

Create Table Employees
(
EmpID varchar(10),
EmployeeName varchar(50)
)

go

--Put some values in there

INSERT INTO Employees
VALUES
('1','John Doe')
INSERT INTO Employees
VALUES
('2','Jane Doe')
go

CREATE PROCEDURE usp_QuoteTest
@EmpList Varchar(100)
AS
SELECT EmployeeName, FROM Employees
WHERE EmpID IN
(@EmpList)

EXEC uspQuoteTest @EmpList = "strVarList"

Now I have had no problem doing this when the EmpID field is an integer. However, when the list is a char or varchar field it is a problem. The each value passed needs to be enclosed by single quotes. I have tried passing the value list enclosed with single quotes. How could I loop through the list of EmployeeIDs and enclose each one with single quotes in the stored procedure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources