Issac, I hope you understand that CURSORS are not the FASTEST thing in SQL Server.. i have personally written many cursors apps to do BATCH Processing.. but i wouldnt recommend SQL Server Cursors for a REAL TIME processing.. Well in Oracle its a different Story... Depends on the DB. I agree T-SQL in <cfquery> doesnt look clean.. but for this situation... i think it will run faster.
The other option would an SP that takes a string of columns names and do while loop constructing the query and then run it with Exec/sp_executeSql(@sqlStmt) or some like that... (Dynamic query SP's .. i like them) Joe > -----Original Message----- > From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 18, 2002 10:08 PM > To: CF-Talk > Subject: Re: Returning columns with null values when specific columns > are unknown at runtime > > > > Why do u have to do the below... u can contruct the SQL in > > CF and just pass > > it. > > > select * from TableName where 1=0 > > This gives you Query.ColumnList.. now.. all you got to do > > is loop through > > the Query.ColumnList and Dynamically build the CASE > > Statement for the Query > > <cfquery ... > > declare @fields varchar(200) > > set @fields=''; > > select > > <cfloop index="x" list="#Query.ColumnList#" delimiter=","> > > @fields=@fields + CASE WHEN #x# IS NULL THEN '#x#' END, > > </cfloop> > > from TableName > > select @fields as ColumnName > > </cfquery> > > > Havent tested this.. but something of this sort might be > > easier. > > > Joe > > It encapsulates the function so that if she needs to use it > elsewhere in the > app or on another table, it's faster and easier to implement than cutting > and pasting ... more portable, etc... I suspect it's also more > efficient and > generally speaking I dislike using multiple t-sql statements in > cfqueries... > although this last is probably just a personal prejudice -- it just looks > bad to me... > > Isaac > Certified Advanced ColdFusion 5 Developer > > www.turnkey.to > 954-776-0046 > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
