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.
The VFP code looks like this:
vCol2Use = Alltrim(Results4.Col2Use)
Insert Into MrrOutPut
(NTIMEID,cMedia,cClient,cProduct,cEstimate,cAccess,cStation,cmarket,&vCol2Us
e) ;
VALUES
(Results4.NTIMEID,Results4.cMedia,Results4.cClient,Results4.cProduct,Results
4.cEstimate, ;
Results4.cAccess,Results4.cStation,Results4.cmarket,Results4.TotResps)
What I'm trying to avoid in SQL is something like this
Set @vCol2Use = (select Col2Use from #Results4 where
results4id=@myresults4id)
Case
When @vCol2use = 'RESP01' then
Begin
Insert Into MrrOutPut
(NTIMEID,cMedia,cClient,cProduct,cEstimate,cAccess,cStation,cmarket,RESP01)
;
VALUES
(Results4.NTIMEID,Results4.cMedia,Results4.cClient,Results4.cProduct,Results
4.cEstimate, ;
Results4.cAccess,Results4.cStation,Results4.cmarket,Results4.TotResps)
End
When vCol2use = 'RESP02' then
Insert Into MrrOutPut
(NTIMEID,cMedia,cClient,cProduct,cEstimate,cAccess,cStation,cmarket,RESP02)
;
VALUES
(Results4.NTIMEID,Results4.cMedia,Results4.cClient,Results4.cProduct,Results
4.cEstimate, ;
Results4.cAccess,Results4.cStation,Results4.cmarket,Results4.TotResps)
...
...
When vCol2use = 'RESP80' then
Insert Into MrrOutPut
(NTIMEID,cMedia,cClient,cProduct,cEstimate,cAccess,cStation,cmarket,RESP80)
;
VALUES
(Results4.NTIMEID,Results4.cMedia,Results4.cClient,Results4.cProduct,Results
4.cEstimate, ;
Results4.cAccess,Results4.cStation,Results4.cmarket,Results4.TotResps)
end
For things like this, the Fox just rocks...
Lou
-----Original Message-----
From: [email protected] [mailto:[email protected]] On
Behalf Of Stephen Russell
Sent: Wednesday, January 19, 2011 2:44 PM
To: [email protected]
Subject: Re: macro substitution in T-SQL
On Wed, Jan 19, 2011 at 4:07 PM, Lou Syracuse <[email protected]>
wrote:
> Ok it doesn't exist as we know it in VFP, but there has to be a way to do
> something like this. I have a variable that has the name of a field in
it.
> I want to retrieve the value of that field from a table
>
>
>
> Something like:
>
>
>
> Declare @vCol2Use as char(10)
>
> Declare @MyValue as integer
>
> Set @vCol2use = 'RESP01'
>
> Set @MyValue = (Select &@Myvalue from mydatatable)
>
> The values for @vCol2use go up to RESP80. This is already the storedproc
> from h3ll, there has to be a way to not hand-code a block of 80 case
> statements. I am converting a VFP program to a SQL call in preparation
> for our SQL update, and have been reminded how cool some of the string
> manipulation and database functions are in VFP.
--------------------
Not really.
You will have to define a STRING and populate the STRING not just copy
the text to a string. This means datetime values need to be converted
for use and you preface them with a ' and then terminate with the ' as
well. :)
Try this site for a good overall method:
<http://www.dba-sql-server.com/sql_server_tips/t_super_sql_460_dynamic_sql.h
tm>
I have done this in SPs before, well really I manufactured a statement
via cursor metadata. Shoot me now for even thinking of it. PLEASE!
What are your case statements? that # you stat seems real high but
you cold be in ETL hell and you just live with it.
--
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/027101cbb82d$1947b500$4bd71f00$@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.
