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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
