asked
Confusion in dynamic query MS SQL 2012
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 lots of data means table have million, billion rows. ![alt text][1] ![alt text][2] 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.** [1]: /storage/temp/2281-untitled.jpg [2]: /storage/temp/2282-untitled-2.jpg
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
Of the two, the first one is better. Please see [Erland Sommarskog's blog ][1] on dynamic SQL. Ideally for this you would use sp_executesql, code sample below: DECLARE @studentName NVARCHAR(100); DECLARE @studentEmailId NVARCHAR(100); DECLARE @SQL NVARCHAR(MAX); SET @SQL = N' SELECT * FROM dbo.tblStudent ts where 1=1'; IF ( @studentEmailId <> '' ) SET @sql += ' AND ts.studentEmailId = @studentEmailId'; IF ( @studentName <> '' ) SET @sql += ' AND ts.studentName = @studentName'; EXEC sp_executesql @SQL, N'@studentName NVARCHAR(100),@studentEmailId NVARCHAR(100)', @studentName = @studentName, @studentEmailId = @studentEmailId; [1]:
http://www.sommarskog.se/
Hi Rohan The two queries are different, the first is comparing to an empty string: IF (@studentEmailId '') The second is using the COALESCE function to display the first non null value WHERE ts.studentName = COALESCE(@studentName, ts.studentName) AND ts.studentEmailId = COALESCE(@studentEmailId, ts.studentEmailId) I guess it should be IF (@studentEmailId IS NULL) @Conan makes a valid point on dynamic SQL but you're not using that, you're using a series of IF statements to obtain an optimal where clause - very different. A couple of points to note, email addresses can be varchar as they aren't allowed the extra characters offered by nvarchar anyway. If it is an id, as the name suggests, then it should be a number ie int. So, back to the question of which is best; I'd go with the first one, although you'll need to check it's doing what you'd expect and only return the rows you need. The reason being is that it will respond to indexing much better, COALESCE is non-sargable. You should always test in your environment though, prove the reason why one is better :) Forgot to mention, you can have one statement with a where clause like this: WHERE ( ts.studentEmailId = @studentEmailId OR @studentEmailId is NULL ) AND ( ts.studentName = @studentName OR @studentName IS NULL )
17 People are following this question.
