Click here to Skip to main content
15,896,269 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hello,

I am stuck between dynamic query and simple query.
From Google I got the solution for simple query with dynamic condition in where clause.
But now I have one confusion for this simple query which have dynamic where clause.
See below details.

I have below a table and two queries in SP.
Here assume that table have million, billion rows.
SQL
SELECT ts.studentId, ts.studentCity,
 ts.studentState, ts.studentCountry,
 ts.studentPhoneNo, ts.studentEmailId,
 ts.studentAddress, ts.studentName FROM dbo.tblStudent ts

Data is like
studentId	studentName	studentAddress	studentEmailId	studentPhoneNo	studentCountry	studentState	studentCity
1	xyz	xyz address	xyz@xyz.com	123456	country	state	city

1)
SQL
DECLARE @studentName nvarchar(100)
DECLARE @studentEmailId nvarchar(100)

If(@studentEmailId <> '')
BEGIN
       SELECT * FROM dbo.tblStudent ts WHERE 
       ts.studentName = @studentName
END
ELSE If(@studentName <> '')
BEGIN
       SELECT * FROM dbo.tblStudent ts WHERE 
       ts.studentEmailId = @studentEmailId
END
ELSE
BEGIN
       SELECT * FROM dbo.tblStudent ts WHERE 
       ts.studentEmailId = @studentEmailId AND ts.studentName = @studentName
END

2)
SQL
DECLARE @studentName nvarchar(100)
DECLARE @studentEmailId nvarchar(100)
SELECT * FROM dbo.tblStudent ts 
WHERE 
ts.studentName= COALESCE(@studentName,ts.studentName) AND
ts.studentEmailId= COALESCE(@studentEmailId,ts.studentEmailId)

Here also assume that in some case second query have multiple condition in where clause.

My question is which one is faster and better to use?

FYI: I am using MS SQL 2012.
Posted
Updated 26-Mar-15 20:18pm
v2
Comments
Saral S Stalin 27-Mar-15 2:23am    
Both queries are not similar. First one checks for '' and second one checks for NULL. Also in your second query assume both parameters are suppiled as NULL, then it will return all the records in the table. Is that the behaviour you want?
NavjotSingh_013 9-Apr-15 3:30am    
COALESCE is function which handle NULL values and takes much more time.

You can actual run your both queries in batch and include actual execution plan before executing queries and see which query consume much more time.
The one which uses COALESCE will take 99% of time relative to batch.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900