Search Query

[R. K. Wijayaratne]

Hi everyone,

I would like to select from table Users where the text field
AboutMeText is null or not null based on a passed in flag value
@IsAboutMeTextNotNull. How can this be achieved?

create procedure Search
@IsAboutMeTextNotNull bit
as

select * from Users
where AboutMeText -- ???

[Erland Sommarskog]

Post by R. K. Wijayaratne
I would like to select from table Users where the text field
AboutMeText is null or not null based on a passed in flag value
@IsAboutMeTextNotNull. How can this be achieved?
create procedure Search
@IsAboutMeTextNotNull bit
as
select * from Users
where AboutMeText -- ???

The easiest would of course to use a IF statement:

IF @IsAboutMeTextNotNull = 1
SELECT ... FROM Users WHERE AboutMeText IS NOT NULL
ELSE
SELECT ... FROM Users WHERE AboutMeText IS NULL

If you insist on doing it in one statement, you can do:

SELECT ...
FROM Users
WHERE (@IsAboutMeTextNotNull = 1 AND AboutMeText IS NOT NULL OR
@IsAboutMeTextNotNull = 0 AND AboutMeText IS NULL)

And never use SELECT * in production code.

[R. K. Wijayaratne]

Great, thank you very much for your help on this Erland ...

Post by R. K. Wijayaratne
I would like to select from table Users where the text field
AboutMeText is null or not null based on a passed in flag value
@IsAboutMeTextNotNull. How can this be achieved?
create procedure Search
as
    select * from Users
    where AboutMeText -- ???

     SELECT ... FROM Users WHERE AboutMeText IS NOT NULL
   ELSE
     SELECT ... FROM Users WHERE AboutMeText IS NULL
    SELECT ...
    FROM   Users
And never use SELECT * in production code.
--
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx