>Where p.PermissionID NOT LIKE '%1%'
>but I pointed out that it had some serious holes in it such as it would
fail
>11 or 21. I've fixed it temporarily by using a series of AND NOT LIKE
>statements with wildcards to fail '1', '1,' or ',1' but pass everything
I think you have a couple of options here, although I haven't though all
the way through this problem.
1) Use Like ',1,%' this way you are forced to have a comma....but you
will have to some special circumstances for the first and last number
in the list ...probably pretty ugly, unless you can change the structure
to always start and end with a comma ... using aliases like ',' +
myColum + ',' as permissionList might help you here, but if I had to
guess you'd end up with temp tables anyway.
2) You might be able to do some creative things with temp tables ...that
way the client dosen't feel like you are changing the DB structure.
3) You might be better off pulling the data back into CF and using list
functions on the data.
4) Convince the client of the right way to develop applications. As the
developer that's what you're their for ...but some situations don't
allow for this <sigh>
5) Ah ha ....5 minutes into this eMail and it hit me .... use the
CHARINDEX function it "Returns the starting position of the specified
expression in a character string". So you could do something like ....
@myPermission int <--- input parameter of the SP
SELECT someID,
someName,
CHARINDEX(@myPermission, (',' + permissionList +
',') ) AS permissionFound
CHARINDEX( cast(@myPermission as
varchar(20)),
(',' + permissionList + ',')
) AS permissionFound
FROM permissionTable
WHERE permissionFound > 0
This little trick should work for you ....although the real problem here
is that no Index will be used ...were talking TABLE SCAN <see
recommendation 4>
Hope this helps,
-eric
------------------------------------------------
Common sense is genius dressed in its working clothes.
-- Ralph Waldo Emerson
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: Tim Fields [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 02, 2001 8:07 PM
To: CF-Talk
Subject: SQL Server data type question...
I have a client that has an existing SQL Server database which includes
a
table used as part of a permissions framework. The problem is that
whoever
created the table was clever enough to create a column for permissions
id's
as a varchar rather than creating a join table. So I may a situation
where
Permissions.PermissionID = "1,4,5"
They have been using a statement in SQL Server7 that is something like:
Select p.FName, p.LName
>From Permissions p
Where p.PermissionID <> 1
That worked in 7, but not in 2000 which correctly sees it as a data type
error ( cannot convert...)
They won't let me rebuild that part of the database properly right now
for
reasons that are debatable, but anyway I have to work with this
structure.
Their internal person came up with:
Where p.PermissionID NOT LIKE '%1%'
but I pointed out that it had some serious holes in it such as it would
fail
11 or 21. I've fixed it temporarily by using a series of AND NOT LIKE
statements with wildcards to fail '1', '1,' or ',1' but pass everything
else. It's a pretty damned ugly statement though if I do say so myself.
Does anyone know of a more elegant solution? I can't quite get my arms
around sp_executesql to see if that could help.
Any help would be appreciated.
Tim
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 02, 2001 11:50 AM
To: CF-Talk
Subject: RE: Stored Procedures and HTML form List
> > To the best of my knowledge, you can't do this. What you can
> > do to get the same effect, though, is to execute a string
> > containing your SQL statement ...
> > ...
> > Of course, this isn't an optimal solution.
>
> Thanks! That's almost exactly what I was looking for. I'll
> try it today. Why would it not be an optimal solution? Is it
> slower or can that function only be used in certain situations?
It's slower, because it can't take advantage of existing query plans as
well.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
