Unfortunately I don't know what this value will be in the UI. The value is dynamic; it is calculated based on other values inside the equivalent of 3 vfp scan loops, and could change for each record that is processed. It's a complex calculation as you can tell by it being 3-deep in scan loops, right now the SP is over 200 lines and I still have more to convert.
Looks like I may just have to bite the bullet and go with the case statements in this instance. I can't just get the data from SQL and change the VFP program to handle it as we are migrating all of our code out of VFP and rewriting this process in .NET would be just as challenging (if not more so) and this is a major report used throughout the organization that has to be dead-on accurate every time. Lou -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Russell Sent: Wednesday, January 19, 2011 3:30 PM 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/034f01cbb8ad$0b0b6420$21222c60$@com ** 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.
