Using Dynamic SQL Statements in Stored Procedures

Results 1 to 3 of 3

Thread: Using Dynamic SQL Statements in Stored Procedures

  1. #1 Guest

    Default Using Dynamic SQL Statements in Stored Procedures

    This article is missing a very important method for building dynamic queries inside a stored procedure. In the following example, as given in the article, SQLServer needs to recompile the query every time it is run -- not a big deal on such a simple query. But a more complex one involving sub-queries, views and joins should be done using the method in the second example. This method allows SQL server to precompile the query and execution plan along with the rest of the Stored Procedure<BR><BR>Article Example:<BR>----------------<BR>CREATE PROCEDURE sp_MyFirstDynamicSP ( @LastName varchar(100) )<BR>AS<BR> EXEC(&#039SELECT FirstName, LastName, SSN, Salary<BR> FROM Employee<BR> WHERE LastName LIKE &#039&#039%&#039 + @LastName + &#039%&#039&#039 &#039)<BR><BR><BR>Better Way:<BR>----------------<BR>CREATE PROCEDURE sp_MyFirstDynamicSP ( @LastName varchar(100) )<BR>AS<BR>SELECT FirstName, LastName, SSN, Salary<BR> FROM Employee<BR> WHERE LastName LIKE &#039%&#039 + @LastName + &#039%&#039<BR> <BR> <BR>Notice I didn&#039t use the &#039exec&#039 command. Also notice that @lastName didn&#039t need me to specify a string containing quotes.<BR><BR>The benefit here is that SQL Server doesn&#039t need to recompile the query every time it&#039s run -- a process that can actually take a bit of time.

  2. #2
    Jim d. Guest

    Default A bad example, perhaps?

    RJ,<BR><BR>You bring up a good point. The dynamic statement certainly didn&#039t need to be. Perhaps the example didn&#039t want to get in to the messiness of a dynamically generated statement.<BR><BR>Now, if you were dynamically generating your &#039where&#039 clause, that might make sense-- as you might have a variable number of column names, &#039and/or&#039 conditions, etc.. <BR><BR>However, this nullifies many of the reasons for using a stored procedure in the first place.<BR><BR> -No speed gain.<BR> -Business logic still placed in ASP<BR> -Change to DB requires you to retool your ASP&#039s.<BR><BR>As I said... I&#039m an ASP newbie, so if I&#039m missing something fundamental here, somebody please tell me.

  3. #3 Guest

    Default RE: A bad example, perhaps?

    That is correct -- if you have dynamic clauses, then you&#039ll have to use the method in the original example. I occasionally do it that way because it&#039s the only way! :)<BR><BR>I just wanted to point out the &#039other way&#039 since it&#039s more common (at least in my projects).<BR><BR>An additional benefit to placing your queries in stored procedures is that making updates to sites that have multiple front-end web servers only require updating the data server, not every front end. I also feel that it helps &#039sloppy&#039 programmers clean their code up. :)<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts