Does anyone have some advice on how to write a generic SQL statement that will execute on Server?
I have the need to execute a query which could return anywhere from 1 – 51 fields in a table, all based upon the users request. This means I need to generically build this SQL statement to return any number of these 51 fields, but just cannot seem to figure out anyway to make this work.
All works great if I hard-code the SQL statement, but then it cannot be generic. I’ve written code to generically create a text version of the SQL statement, but cannot find a way to execute it without generating errors when it’s run on the server.
For example, here is a hard-coded statement:
SELECT CONVERT(Varchar(12),[TIMESTAMP], 101), CONVERT(Varchar(12),[TIMESTAMP], 108),
WHERE [TimeStamp]>=(CONVERT(datetime,CONVERT(varchar(30),:vt_temp1), 120))
AND [TimeStamp] <= (CONVERT(datetime,CONVERT(varchar(30),:vt_temp2), 120))
INTO :at_temp1, :at_temp2, :ar_gl_hist_em1, :ar_gl_hist_em2, :ar_gl_hist_em3
This will execute perfectly, but if it is built as a text variable, I cannot get it to run. I am looking for a way to make the “SELECT” and “INTO” lines generic.
I’m looking for suggestions from anyone who may have done this…