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.
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)
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)
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.