Passing a String Variable to SQL "IN" Statement

[ibethea]

The problem is explained after the Select statement.
The problem is within the where clause, using "IN(@text)".

ALTER Procedure usp_MilestoneChangeList (@chDays as int, @Text as nvarchar(100))
AS

SELECT DISTINCT t_project.[JDE Proj Num], t_project.projectReportdesc, t_project.PGCode, t_PGcode.SUBPROG, vwcurStaff.LastFirst, vwcurStaff.rptName, vwcurStaff.Description AS Title, t_institution.INSTNAME, msDesc.Description AS MilestonDesc, t_milestoneSchedule.DateStamp AS [Changed On], t_milestoneSchedule.Date, t_milestoneSchedule.Author, t_milestoneSchedule.[LogIn ID]
FROM ((t_PGcode INNER JOIN (t_project INNER JOIN vwcurStaff ON t_project.[JDE Proj Num] = vwcurStaff.[JDE Proj Num]) ON t_PGcode.PGCODE = t_project.PGCode) INNER JOIN (t_milestoneSchedule INNER JOIN t_codes AS msDesc ON t_milestoneSchedule.[Milestone ID] = msDesc.Code) ON t_project.[JDE Proj Num] = t_milestoneSchedule.[JDE Proj Num]) INNER JOIN (t_projectLocation INNER JOIN t_institution ON t_projectLocation.INSTCODE = t_institution.INSTCODE) ON t_project.[JDE Proj Num] = t_projectLocation.JDEPROJNUM
WHERE ((vwcurStaff.Description='Project Manager') AND (t_milestoneSchedule.DateStamp>=(getdate()-@chDays)) AND (t_project.projectStatus='1' Or t_project.projectStatus='3') AND (t_project.PGCode IN (@Text)))
ORDER BY t_milestoneSchedule.DateStamp DESC

The @Text is sent '12', '23', '18', '34', '65', '70'
I have tried to send "'12', '23', '18', '34', '65', '70'"
I have tried to send ''12', '23', '18', '34', '65', '70''
I have concatenated single and double qoutes at the ends.
I have tried changing @text to varchar and char.

If I send separate variables for each value the "IN" statement works; IN(@text1, @text2, @text3, ..., @textn).
However, I cannot be certain what the upper limit is for n so I wanted to send one string.

Note: I am using MS Access 2000 .adp against an SQL Server 2000 database.

Any Ideas...

 

[JamesLean]

Please see this FAQ:

Passing a list of values to a Stored Procedure
faq183-3979

--James

 

[Glyndwr]

ibethea

try this

declare @item int
declare @text varchar(8000)
declare @tab table (num int)

set @text = '12, 23, 18, 34, 65, 70'
set @item = 1
while @item <>0
	begin 
	 set @item =   charindex(',',@text)
	 if @item <> 0 
		 begin 
			 insert into @tab values(substring(@text,1,charindex(',',@text)-1))
		 end
	 else
		 begin
			 insert into @tab values( @text)
		 end
	 if @item <> 0
		 SET @text = substring(@text,charindex(',',@text)+1,len(@text) - charindex(',',@text))
	end

then replace

t_project.PGCode IN (@Text)
with
t_project.pgcode in (select num from @tab)


OR preferable
inner join @tab t on t.num = t_project.pgcode