Integer, varchar and IN

In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname
from category
where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."

Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?

(same question is also posted at sql-server-performance.com forums)Originally posted by Frettmaestro
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?there are several ways, all of them, um, how do i put this, less than elegant
(same question is also posted at sql-server-performance.com forums) i'd be interested in hearing about any more clever solutions than mine:select C.categoryname
from user U
inner
join category C
on instr(','+C.catid',', ','+U.categories+',') > 0
where U.userid = 12the concatenation of a comma in front of and behind the fields is so that you will find the first or last category in the list

and of course the search string is delimited by commas, so that you don't accidentally find 12 inside 312|||Thanx alot for the contribution...are you a vb/asp-developer? This syntax is alot like asp/vb but unfortunately there is no such thing as "instr" in sql. I have used InStr alot in my asp-scripting but "IN" and "LIKE" are the only keywords I can think of that does close to the same thing allthough I cannot make them work. It's not a big deal to make the extra table actually...it was really more out of curiosity I was asking...|||vb/asp? me? heh, no way, not even close

my mistake, change INSTR to CHARINDEX|||but yeah, create a normalized table

for one thing, your queries won't do table scans and can start using indexes

:cool:|||This is the final version and it worked like a charm allthough I think I will go with the normalized table as you are recomending.select C.category
from user U
inner
join category C
on CHARINDEX(',' + CAST(C.catid AS varchar(20)) + ',', U.categories) > 0
where U.userid = 12It really doesn't make that big difference because this will probably only be used by 3-4 users maybe a couple of times a week, but still I prefer to create quality stuff instead of shit when I can. But thanx alot buddy for all your help, I might be back to bother you again real soon :D|||you have to put commas around U.categories too, otherwise you won't find the first or last item

;)

Integer Parameter

I have a parameter field with a data type of int. I want the users to be able to select one or 'all' from the dropdown list. Is there a wildcard for a integer datatype?

Dropdown list example:

All Project Ids

1001

1002

1003

1004

In cases like this I will frequently use a NULL parameter to designate 'all'; can that work for you in this case? An example of this would be something like this:

declare @.example table (integerCol integer)
insert into @.example values (1001)
insert into @.example values (1002)
insert into @.example values (1003)
insert into @.example values (1004)

declare @.anIntegerParm integer
set @.anIntegerParm = null
set @.anIntegerParm = 1002

if @.anIntegerParm is null

select * from @.example

else

select * from @.example
where integerCol = @.anIntegerParm

-- When @.anIntegerParm is null:

-- integerCol
-- --
-- 1001
-- 1002
-- 1003
-- 1004

-- When @.anIntegerParm = 1002:

-- integerCol
-- --
-- 1002

select * from @.example
where @.anIntegerParm is null
or @.anIntegerParm is not null
and integerCol = @.anIntegerParm

-- integerCol
-- --
-- 1002