> 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?
Sorry about that... maybe more descriptive parameter names are in order...
:) was in a hurry... @pk being of data type "sysname" indicates the name of
the primary key column for the table, while @uid ( unique identifier ) being
of data type int indicates the value to find in the column indicated by @pk
to find your single row... Essentially, these two parameters are a key/value
pair.
So if you wanted to find user 1 out of your dbo.tUsers table, in query
analyzer you would use
exec sp_fetchNullColumnNames
@tablename = 'tUsers',
@pk = 'userid',
@uid = 1
You might actually want to change the data type of the @uid column to
nvarchar(50) and remove the convert() function further down, since this will
both remove an unnecessary function ( slightly more efficient ) and make the
sp more versatile since it will be able to handle non-numeric primary key
values as well... Also make sure that if your table doesn't belong to the
dbo that you specify the table owner in the @schema parameter.
What it does is create a temporary table, which it populates with the names
of all the columns in the table you're testing. It then loops over the items
in that table and checks to see if the corresponding value in the specified
row of your test table is null.
/*( Ah! change the IS NULL to IS NOT NULL -- sorry about that, my bad )*/
If not it deletes the name of the column from the temp table. Finally it
selects all column names from the temp table which is then deleted.
The end result will be one row in your recordset for each column which is
null, containing the name of the column in a column named column_name. ...
wow, that makes a lot of sense. :)
Let me know when you implement it and if you have any other questions /
problems, I'd be interrested to know how it turns out. :)
S. Isaac Dealey
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
> -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
>>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
