Add a where condition only if a parameter exists

I'm pretty new to TSQL. I'm trying to write a generic proc that returns
either ALL records from a table or (if a parameter is set to 1) or only a
sub-set of that table (if parameter is set to 0).
Here's an example of what I'm aiming for:
Create table contact (
contactID int,
FullName varchar (40),
IsActive bit)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
-- if @.ShowAll is null, return all records
--otherwise limit records to records where isActive = True
How can I conditionally add a WHERE clause based on the value of @.ShowAll?
Thanks!
- JohnnyI presume that ContactId is the prinary key of Contacts. If this is the case
,
skip the @.ShowAll parameter.
If @.ContactId is null then have it return all, otherwise have it return only
that contact:
SELECT *
FROM Contacts
WHERE ContactId = COALESCE(@.ContactId,ContactId)
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"JohnnyMagz" wrote:

> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||You need dynamic sql in this case
something like (untested)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
declare @.sql varchar(8000)
set @.sql = 'select * from contact'
if @.showall is not null
set @.sql = @.sql + ' where isactive = 1'
exec (@.sql)
go
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||Assuming isActive is never NULL and @.ShowAll is either NULL or 1.
...where isActive = isnull(@.ShowAll, 0) or isActive = @.ShowAll
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny