How to simplify too many if conditions..

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to simplify too many if conditions..

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-09 : 09:12:44

Davey writes "I have here a sample stored proc with 2 parameters...which I will came up with 4 conditions to satisfy the parameters....so is there a way to simplify it to just one query or is there a short cut coz i might be having 6 parameters which will have 36 conditions which is very long...

here is my sample query...Thanks guys in advance...=)

create procedure [dbo].[Sp_Test]

@name nvarchar(30),
@address nvarchar(30)

as

--1. parameters value = 'ALL'

if @name = 'ALL' and @address = 'ALL'

begin

SELECT * FROM Emp_Address , Emp_Name
WHERE
Emp_Name.Employee_Id = Emp_Address.Employee_Id
and Emp_Address.Address in (SELECT Address FROM Emp_Address)
and Emp_Name.Employee_Name in (Select Employee_Name from Emp_Name)

end

--2. address not = 'ALL'

if @name = 'ALL' and @address != 'ALL'

begin

SELECT * FROM Emp_Address , Emp_Name
WHERE
Emp_Name.Employee_Id = Emp_Address.Employee_Id
and Emp_Address.Address in (SELECT Address FROM Emp_Address where Address = @address )
and Emp_Name.Employee_Name in (Select Employee_Name from Emp_Name)

end

--3.name not = 'ALL'

if @name != 'ALL' and @address = 'ALL'

begin

SELECT * FROM Emp_Address , Emp_Name
WHERE
Emp_Name.Employee_Id = Emp_Address.Employee_Id
and Emp_Address.Address in (SELECT Address FROM Emp_Address)
and Emp_Name.Employee_Name in (Select Employee_Name from Emp_Name where Employee_Name = @name )

end

--4. name and address not = 'ALL'

if @name != 'ALL' and @address != 'ALL'

begin

SELECT * FROM Emp_Address , Emp_Name
WHERE
Emp_Name.Employee_Id = Emp_Address.Employee_Id
and Emp_Address.Address in (SELECT Address FROM Emp_Address where Address = @address)
and Emp_Name.Employee_Name in (Select Employee_Name from Emp_Name where Employee_Name = @name)

end"

alanlambert
Starting Member

26 Posts

Posted - 2006-08-09 : 09:38:24

Try using the COALESCE function.

I would also think you can do away with the subqueries.

A couple of other tips: Don't start your stored procedure names with 'sp_' as it has an impact on performance if they're stored anywhere other than the master database. ([url]http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html[/url]). Also, it's better to specify exactly which fields you want returned rather than using select * ([url]http://www.asp101.com/tips/index.asp?id=125[/url])

CREATE PROCEDURE [dbo].[My_Test]
@name nvarchar(30),
@address nvarchar(30)
As

IF @name='ALL' SET @name=NULL
IF @address='ALL' SET @address=NULL

SELECT * 
FROM Emp_Address JOIN Emp_Name
ON Emp_Name.Employee_Id = Emp_Address.Employee_Id
WHERE Emp_Address.Address = COALESCE(@address,Address)
	And Emp_Name.Employee_Name = COALESCE(@name,Employee_Name)

Each time you add a new parameter include the single 'IF' line and add a new condition to the select statement using coalesce.

Hope that helps

Alan

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-08-09 : 09:55:30

I don't know we can do this in SQL Server or not but when I was using java we used visitor pattern to replace if else statement.
Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 12:36:45

Try this...

SELECT * FROM Emp_Address , Emp_Name
WHERE
Emp_Name.Employee_Id = Emp_Address.Employee_Id
and Emp_Address.Address in (SELECT Address FROM Emp_Address WHERE 1 = (CASE 
										when @address = 'ALL' then 1 
										else 
											(case when address = @address then 1 else 0 end)
										end)
and Emp_Name.Employee_Name in (Select Employee_Name from Emp_Name where 1 = (case when @name = 'ALL' then 1 
											else 
												(case when Employee_Name = @name then 1 else 0 end)
											end)

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources