Gee thanks! Ok, I will. I'll have to wait a couple of days, though. While
I'm offsite I don't have access to the SQL server due to the client's rather
anal security.
I've read it through a couple of times and I'm pretty sure I see what's
going on. The only question I have, though it may be obvious, is to check
if I'm to pass in the @tablename, @pk, @uid values to the procedure like I
think I am. And what is the difference between the @pk and @uid variables?
@pk is primary key, yes? Is @uid the same value?
-Patti
----- Original Message -----
From: "S. Isaac Dealey" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, September 18, 2002 2:17 PM
Subject: Re: Returning columns with null values when specific columns are
unknown at runtime
> 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
>
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
