Couldn't you use Permissions.PermissionID IN " '4','5' "
Tim Fields wrote:
> 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 =
>
> 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
