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
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
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
