quote:

---

Originally posted by madhivanan

Surendra,

If c_orderno is of Varchar datatype then the values at @list will come as data1,data2,etc without single quotes. You need to handle that.

Refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=58867

Madhivanan

Failing to plan is Planning to fail

---

set nocount on
--============================================================================
--A simple parsing routine

if object_id('dbo.udf_ParseIntArray') > 0
	drop function dbo.udf_ParseIntArray
go
create function dbo.udf_ParseIntArray(@in varchar(8000))
returns @out table (i int)
as
begin
	declare @idx int
	select	@idx = 1
		,@in = ',' + @in + ','
	while @idx > 0 and @idx < len(@in)
	begin
		insert @out (i)
		select i
		from	(select substring(@in, @idx+1, charindex(',', @in, @idx+1)-@idx-1) i) a

		--make sure it's an int
		where	i not like '%[^0-9]%'
		and	len(i) <= 10 --this ones not fool proof but...eh

		--reset @idx to next delimiter
		set @idx = charindex(',',@in,@idx+1)
	end
	return
end
go
--============================================================================

declare @intArray varchar(8000)
set @intArray = '3,4'

--use not in ()
select n
from	(select 1 n union select 2 union select 3 union select 4) a
where	n not in (select i from dbo.udf_ParseIntArray(@intArray))

--or use a left outer join

select n
from	(select 1 n union select 2 union select 3 union select 4) a
left join dbo.udf_ParseIntArray(@intArray) ex
		on ex.i = a.n
where	ex.i is null

--============================================================================
go
if object_id('dbo.udf_ParseIntArray') > 0
	drop function dbo.udf_ParseIntArray