asked on

Using a List generated from a column in the where clause

I want to add records to a table, which can only be seen by certain groups when viewd in an asp page.

I want to inset a comma deliminated sequence which i intend to use as a list for checking the users against in a select where clause - how do i do this ie

TABLE COLUMNS:ID , News, AuthorisedGroup
SAMPLE DATA : 23 , New Wages For All , [1,2,3]
SQL: select * from table
Where ID in ( AuthorisedGroup )

needles to say this doesn't work - any help pls?

nigelrivett

where ',' + AuthorisedGroup + ',' like '%,' + convert(varchar(20),ID) + ',%'

or use dynamic sql

adathelad

Hi Mike,

EXECUTE ('SELECT * FROM Table WHERE ID IN (' + @AuthorisedGroup + ')')

Cheers

adathelad

(where @AuthorisedGroup is your comma-separated list)

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ispaleny

Here it is

GO
create function dbo.ufn_FromCrossID(@List varchar(50))
returns @t table (ID int primary key)
with schemabinding
as begin
declare @Pos int
declare @NewPos int
set @List=','+@List+','
set @Pos=1
set @NewPos=charindex(',',@List,@Pos+1)
while @NewPos>0 begin
insert @t(ID) values (convert(int,substring(@List,@Pos+1,@NewPos-@Pos-1)))
set @Pos=@NewPos
set @NewPos=charindex(',',@List,@Pos+1)
end
return
end
GO

declare @List varchar(50)
set @List='1,2,3,789,0'
select * from dbo.ufn_FromCrossID(@List)

Good luck!