Post reply

Parameterize all your queries using the sp_executesql - what does it mean

  • January 10, 2011 at 5:38 am

    #233111

    I just read the text below on a site where the author is giving tips for improving performance in SQL Server.

    16. Parameterized queries: Parameterize all your queries using the sp_executesql. This would help the optimizer to cache the execution plans and use the same when requested the second time. You can cache-in the time required to parse, compile and place the execution plan.

    What exactly does the above mean? The only place I have queries is within stored procedures (and views). I have thousands of stored procedures that have parameters which are passed to a query that might say ... Select FirstName, LastName from tblUsers WHERE UserID = @user-id

    Is this wrong?

    The only time I have used sp_executesql is to execute dynamic SQL within a stored procedure.

  • January 10, 2011 at 5:58 am

    #1271339

    your probably doing it right.

    The important thing to remember about sp_executesql is that it is susceptible to SQL Injection. since using parameters defeats that security hole, simply using params everyplace you are using that proc protects you from the issue.

    did you see the comic about Little Bobby Tables?

    SQL Injection Humor

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 10, 2011 at 7:01 am

    #1271362

    They had to have been referring to ad hoc, client side code. A parameterized query or a stored procedure are basically the same thing, so you're already on the right track.

    Just for curiosity's sake, where were you getting this information from?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply