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
>From: S. Isaac Dealey <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: Re: Returning columns with null values when specific columns are
>unknown at runtime
>Date: Wed, 18 Sep 2002 15:32:57 -0400
>
>Ooops... should be "create procedure" ... :)
>
> > Hey Patti,
>
> > Try this stored procedure:
>
> > alter procedure sp_fetchNullColumnNames
> > @tablename sysname,
> > @pk sysname,
> > @uid int,
> > @schema sysname = 'dbo'
> > AS
>
> > declare @colname sysname;
> > declare @strSQL nvarchar(1000);
>
> > create table #c ( column_name sysname);
>
> > insert into #c
> > select column_name from information_schema.columns
> > where table_schema = @schema and table_name = @tablename;
>
> > declare curColumn cursor local fast_forward for
> > select column_name from #c;
>
> > open curColumn;
> > fetch next from curColumn into @colname;
>
> > while (@@fetch_status = 0) begin
> > set @strSQL = ('if ((select ' + @colname + ' from ' + @schema + '.' +
> > @tablename
> > + ' where ' + @pk + ' = ' + convert(nvarchar,@uid) + ') IS NULL) '
> > + ' delete from #c where column_name = ''' + @colname + '''');
> > print @strSQL;
> > exec sp_executesql @strSQL;
>
> > fetch next from curColumn into @colname;
> > end
>
> > close curColumn;
> > deallocate curColumn;
>
> > select * from #c;
>
> > drop table #c;
> > go
>
> >> Ok, I'm willing to give this a try... but I still have a question.
>
> >> My real table has 27 columns. I never know which columns will be null.
> >> Does that mean I want a case statement for each column that could
> >> possibly
> >> be null then?
>
> >> It seems like the select statment here is directly tailored to the
> >> example
> >> I
> >> used below... I will never know that much detail beforehand. I only
>put
> >> the
> >> example to give a visual of what I may need to return.
>
> >> If I send in primary key 1 then I want to return columns 3 and 4, but
> >> only
> >> if they acutally are null. The next time I run the query I'd be
>sendign
> >> in
> >> primary key 2 and in that case I'm returning columns 1, 3 and 4.
>
> >> So, I just wanted to check ... Can you explain how this is actually
> >> working?
> >> Is it relying on my explictly stating which columns I'm returning at
>any
> >> one
> >> time? If it is, then it's still not what I'm looking for. And perhaps
> >> what
> >> I'm looking for isn't possible.
>
>
> > S. Isaac Dealey
> > Certified Advanced ColdFusion 5 Developer
>
> > www.turnkey.to
> > 954-776-0046
> >
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
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
