My beef with it is that you lose most of the security with Dynamic sql since you need to grant permissions on the tables referenced in the dynamic sql vs just giving execute permission on the stored proc. I figure if you've got to use dynamic sql, you might as well use cf and have an easier time creating the sql string.
On Fri, 28 Jan 2005 15:49:41 -0600, Robert Shaw <[EMAIL PROTECTED]> wrote: > > Thanks, can you send me the code for the UDF. I really appreciate it. > > Just curious, other than the fact is not compiled, what is your objection to > a dynamic SQL > > > ----- Original Message ----- > From: Daniel Elmore > To: [email protected] > Sent: Friday, January 28, 2005 1:15 PM > Subject: RE: SQL IN Statement > > > Robert, to use the char string in an SQL IN clause most people would say > just use dynamic SQL (EXEC ...). However, I would never recommend anyone to > use dynamic SQL unless it was an usual circumstance. Instead, I use a UDF > called ListToTable which converts a varchar list to a table variable (not a > temp table!) with rows. Therefore you can write code like so: > > DECLARE @myIDString varchar(100) > SET @myIDString = '2,3,4,5,6,7,8' > > SELECT * > FROM entCustomers > WHERE CustID IN (select ID from dbo.ListToTable(@myIDString) ) > > Does that make sense? If you want the code for the UDF let me know. > > Daniel Elmore > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Robert Shaw > Sent: Friday, January 28, 2005 10:25 AM > To: [email protected] > Subject: SQL IN Statement > > > > I have a cookie that hold multiple ID numbers for specific jobs. I want to > pass that through a storedProcedure to the server to use as an IN clause. I > have to pass the string as text, but the value on the SQL server site has to > be numeric. > > I can write the entire query string and pass it to SQL server but is there a > way to just pass the values needed for the IN clause. > > What am I missing?. > > > > > > Robert Shaw > Edata Alliance, Inc. > www.EdataAlliance.com > DataOnTheWeb > www.DataOntheWeb.com > 972-463-3515 > 972-463-9215 > 972-463-9896 -- G-mail must not realize I'm and anti-social nerd, it keeps telling me to "Invite 8 friends to Gmail" ---------------------------------------------------------- To post, send email to [email protected] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
