Case Statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case Statements

Author

Topic

charlesm
Starting Member

2 Posts

Posted - 2007-06-20 : 10:57:49

I am creating a stored procedure which receives the following 4 variables. However, VacancySectorID is the only madatory variable.

@VacancySectorID int = NULL, (mandatory)
@VacancyRegionID int = NULL, optional)
@VacancyTypeID int = NULL, (optional)
@VacancyKeywords nvarchar(64) = NULL, (optional)

My objective is to dynamically build the WHERE statement which in turn will return either one or more of the variables and the required data. The following code works for a single if else but it appears that you cannot have more than one if else statement.

Anyones input would be greatly appreciated.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Public_GetVacancyListingBySearchCriteria]
(
@VacancySectorID int = NULL,
@VacancyRegionID int = NULL,
@VacancyTypeID int = NULL,
@VacancyKeywords nvarchar(64) = NULL
)
AS
-- SET NOCOUNT ON;
IF @VacancySectorID IS NULL AND @VacancyRegionID Is NULL AND @VacancyTypeID IS NULL AND @VacancyKeywords IS NULL
BEGIN
SELECT viewVacancies.*
FROM viewVacancies
WHERE VacancyActive = 1 AND VacancySectorID = @VacancySectorID
ORDER BY VacancyPosted DESC, VacancyTitle
END
ELSE
BEGIN
SELECT viewVacancies.*
FROM viewVacancies
WHERE VacancyActive = 1
ORDER BY VacancyPosted DESC, VacancyTitle
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-20 : 11:01:50

More than one IF...ELSE is definitely allowed.

if (condition)
begin
...
end
else if (condition)
begin
...
end
else if ...
begin
...
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

charlesm
Starting Member

2 Posts

Posted - 2007-06-20 : 11:57:33

Thanks for your reply.

Is there a more efficient way of doing this then having to write the IF statements for every possible combination / variation?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-20 : 12:02:05

[code]SELECT viewVacancies.*
FROM viewVacancies
WHERE VacancyActive = 1 AND
VacancySectorID = Coalesce(@VacancySectorID, VacancySectorID) AND
VacancyRegionID = Coalesce(@VacancyRegionID, VacancyRegionID) AND
...
ORDER BY VacancyPosted DESC, VacancyTitle[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-20 : 12:13:23

Or even this:


SELECT <colulms>
FROM <Table >
WHERE (@VacancySectorID IS NULL OR VacancySectorID = @VacancySectorID) 
	AND (@VacancyRegionID IS NULL OR VacancyRegionID = @VacancyRegionID) 
	AND ..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources