I figured I'd jump in late and add my $.02 worth! The following will return
a result set with all of the null columns values. If you need the names you
can always retrieve that in CF (query.ColumnList).
CREATE PROCEDURE rGetNullColumns
@tnPrimaryKey int
AS
SET NOCOUNT ON
DECLARE @lcSQL varchar(1000)
SELECT @lcSQL = 'SELECT '
+ CASE WHEN col1 IS NULL THEN 'col1,' ELSE '' END
+ CASE WHEN col2 IS NULL THEN 'col2,' ELSE '' END
+ CASE WHEN col3 IS NULL THEN 'col3,' ELSE '' END
+ CASE WHEN col4 IS NULL THEN 'col4,' ELSE '' END
+ '0 AS DummyField '
+ 'FROM tablename WHERE primarykey = '
+ CAST(@tnPrimaryKey AS varchar)
FROM tablename
WHERE primarykey = @tnPrimaryKey
EXEC (@lcSQL)
______________________________________________________
Bill Grover
Supervisor MIS Phone: 301.424.3300 x3324
EU Services, Inc. FAX: 301.424.3696
649 North Horners Lane E-Mail: [EMAIL PROTECTED]
Rockville, MD 20850-1299 WWW: http://www.euservices.com
______________________________________________________
> -----Original Message-----
> From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 18, 2002 11:58 AM
> To: CF-Talk
> Subject: Re: Returning columns with null values when specific columns
> are unknown at runtime
>
>
> Sorry, but you made the same mistake that simon did.
>
> I don't want to return select *
>
> I want to return select (only columns that are null and I
> don't know what
> they are up front) where primarykey = some number
>
> I didin't actually have any questions regarding the where
> statement at all.
> I'm aware that I'll probably have to use the whole IS NULL
> dealie... but
> where I'm stuck is how do I selectively return columns when I
> don't know
> which columns fit my criteria (containing null values only).
>
> Thanks - Patti
> ----- Original Message -----
> From: "Candace Cottrell" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, September 18, 2002 11:23 AM
> Subject: Re: Returning columns with null values when specific
> columns are
> unknown at runtime
>
>
> > How about this.... or am I still missing something
> >
> > Select * from TABLENAME
> > WHERE col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL
> >
> >
> > Candace K. Cottrell, Web Developer
> > The Children's Medical Center
> > One Children's Plaza
> > Dayton, OH 45404
> > 937-641-4293
> > http://www.childrensdayton.org
> >
> >
> > [EMAIL PROTECTED]
> >
> > >>> [EMAIL PROTECTED] 9/18/2002 11:17:41 AM >>>
> > Thanks, but that doesn't get me where I want.
> >
> > I ONLY want to return the columns that ARE null, and I
> never know which
> > ones
> > those are when I run this query... so what I need is
> something for the
> > select statement.
> >
> > -Patti
> > ----- Original Message -----
> >
> > > The only thing I can think to do is create a bunch of OR
> statements
> > i.e.:
> > > WHERE colA IS NULL OR colB IS NULL OR colN IS NULL
> > >
> > > ~Simon
> > >
> > >
> > > -----Original Message-----
> > >
> > >
> > > Is there a way to write a MSSQL 2k query that will return a result
> > set
> > that
> > > contains only columns with null values when you don't know
> > explicitly
> > which
> > > columns those will be?
> > >
> > > So if I have this data
> > >
> > > pk | col 1 | col 2 | col 3 | col 4|
> > > 1 1 2 null null
> > > 2 null 4 null null
> > >
> > > I'd like a query that would return col 3 and col 4 where pk = 1 or
> > col 1,
> > > col 3 and col 4 where pk=2.
> > >
> > > Is this possible?
> > > -Patti
> >
> >
> >
> >
>
______________________________________________________________________
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
