Here's the execution plan, now what? (Part 3)

[@venkynew9509]

Thank you so much Hugo! I have one question.. if dynamic query maintained in application side rather than stored procedure, do you think this will reduce the performance? I mean ,will stored procedures with dynamic sql help us in performance?

[@HugoKornelis]

Thanks for your question!
Dynamic SQL *can* (but does not always) help, in some specific circumstances. As explained in the video, a search procedure with many optional search parameters is an example where dynamic SQL *might* help.
From a performance perspective, it does not matter whether you build the dynamic SQL in a stored procedure and submit it through sp_executesql, or build the dynamic SQL in the application code and submit it through for example the ExecuteReader method of the SqlCommand class.
From a security / safety aspect, it DOES matter how you build the query. As also mentioned in the video, no user input should ever land in the query string. Everything that you put in the query should be hardcoded query fragments in your own code. All parameter values should be passed as parameters, either as SQL parameters if you use sp_executesql (as shown in the video), or as parameter objects of the SqlCommand class.

[@venkynew9509]

@@HugoKornelis Thanks again 👍