Dynamic SQL is actually frowned upon for security reasons. It's fairly easy for someone to do SQL injection which is very dangerous.
Dynamic SQL Injection Articles http://www.google.com/#hl=en&sugexp=ldymls&xhr=t&q=dynamic+SQL+injection&cp= 16&pf=p&sclient=psy&aq=0&aqi=&aql=&oq=dynamic+SQL+injec&pbx=1&fp=2d73bcec2e6 e3c54 Advisor has an article "Prevent SQL Injection Attacks and Gain Performance in Microsoft Visual FoxPro" but unless you are a subscriber, it's not available http://my.advisor.com/doc/18675 John -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Russell Sent: Wednesday, January 19, 2011 18:30 To: [email protected] Subject: Re: macro substitution in T-SQL On Wed, Jan 19, 2011 at 5:03 PM, Lou Syracuse <[email protected]> wrote: > I was looking at dynamicSQL. The table I need to work with is a temp > table in the SP, so I think it would be out of scope in the dynamicSQL > call but I could be wrong as this is new territory for me. > > My hands are pretty well tied on this one. The tables are from a > canned program so I can't change them. > The VFP code I am converting is... challenging, to say the least. Lol > Basically I have a table that includes fields numbered RESP01 to > RESP80, I need to do an insert into that temp table and update only > ONE of those 80 fields. ------------------------------ So in your GUI you know the column that needs to be sent as well as the values for VALUES (Results4.NTIMEID,Results4.cMedia,Results4.cClient,Results4.cProduct,Results 4.cEstimate, Results4.cAccess,Results4.cStation,Results4.cmarket,Results4.TotResps) vCol2Use No big deal. 1. Make an SP to receive all of the params you are passing. Data values as well as column# Make your string for the insert. declare @sql varchar(1000) set @sql = ' Insert into CrapyTable ( columns-----, ' + @klugeColumn+ ') values ( ' @sql = @sql + Now take the param data and string it one at a time or just cluster F it all at once. exec (@sql) Why do you folks make such a big thing out of standard string syntax. This is pretty easy after you spend 10-20 hrs a week on it. Bad Steve! To add a ' mark you may need 3 ''' just as a heads up. running this and seeing what you have done you will find the PRINT @sql line to be your friend. You can copy the OUTPUT of that print over to another window and see if it will execute there. -- Stephen Russell Sr. Production Systems Programmer CIMSgts 901.246-0159 cell [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
