Post reply

Prepared statements or stored procedures?

  • January 28, 2008 at 9:48 pm

    #62768

    which is better Prepared statements or stored procedures? when do we use pepared statements and when do we use stored procs. Our client prefers prepared statements over sps. does the DB have to be denormalized in that case?

  • January 29, 2008 at 12:12 am

    #772738

    When you say "prepared statements" do you mean ad hoc queries? If so, the stored procedure is better. Security: By using only stored procedures you can avoid giving table level permissions. Efficiency: SQL server will cache the execution plan.

  • January 29, 2008 at 12:40 am

    #772742

    I prefer stored procs, from a point of code encapsulation. If the sql code is in the db, then I'm able to tune it if necessary. If the SQL statements are embedded in the client app, then there's no way of changing anything.

    The db certainly does not need to be denormalised for prepared sql statements.

    Also, for prepared sql statements, the user must have select/insert/update/delete rights on the tables. With procs, he user can just have exec rights on the procs and no rights to the base tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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