Professional Web Applications Themes

sp_executeSql - Microsoft SQL / MS SQL Server

I have some questions about this stored procedure. 1. This parameter thing you can send in to the sp_executeSql can only handle things in the WHERE statements i guess? I cant send in for example table names in there? 2. If i build the query with dynamic table names and dynamic where statementsm (if input parameter has a value add this condition and so on) will i still gain benefits from sp_executeSql? Or would i get the same performande overhead as if i used Exec()? 3. If i just send in a SQL statement direct from ASP and then just ...

  1. #1

    Default sp_executeSql

    I have some questions about this stored procedure.

    1. This parameter thing you can send in to the sp_executeSql can only
    handle things in the WHERE statements i guess? I cant send in for
    example table names in there?

    2. If i build the query with dynamic table names and dynamic where
    statementsm (if input parameter has a value add this condition and so
    on) will i still gain benefits from sp_executeSql? Or would i get the
    same performande overhead as if i used Exec()?

    3. If i just send in a SQL statement direct from ASP and then just
    execute it using sp_executeSql do i gain any performance? Is is better
    than using just Exec()?

    Thanx in advance and sorry for my english ..

    /Ola

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ola Guest

  2. #2

    Default Re: sp_executeSql

    sp_executesql will always give you a better chance of sql server re-using
    the query plan than Exec will. It will only be faster than Exec if it can
    reuse the query plan, otherwise they are basically the same. The key is to
    only use exec or sp_executesql when you really need to. Most things don't
    need dynamic sql. Make sure you have a proper design first and then only
    use it where it makes sense. Stored procedures are the way to go over adhoc
    sql from the front end in almost all cases.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Ola Johansson" <nu> wrote in message
    news:%phx.gbl... 


    Andrew Guest

  3. #3

    Default Re: sp_executeSql

    Thanx!

    Damn .. i did some testing in the profiler and tried to run one query
    direct with EXEC(SQL) and then one that i build with parameters and used
    sp_executeSql ..

    when i just runed exec(sql) it did like 230 reads and most of the "runs"
    had a duration at 16 or something (my table is nearly empty right now)
    ..

    When i runned the one with sp_executeSQL duration was 0 for all but the
    first one and reads was 2 ..

    hmm .. seems good ;)

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Ola Guest

  4. #4

    Default Re: sp_executeSql

    Ola Johansson (nu) writes: 

    You use a parameter anywhere T-SQL permits a regular variable. Thus you
    cannot use for a table. (That would be a table variable, but you can
    pass table variables between stored procedures.)


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Replies: 1
    Last Post: November 5th, 01:03 PM
  2. sp_executeSQL Performance in a Proc(?)
    By JDP@Work in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 2nd, 09:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139