EXEC and sp_executesql

  • I wrote a simple select stored procedure with a where clause pointed to a @parameter - no EXEC, no sp_executesql. In query analyzer it worked. It's great to find a free lunch, but is there a gotcha to not using EXEC or sp_executesql?

  • There is no gotcha to not using sp_executesql or exec() afaik.

    This is the way to do it, a parameter in a sp.

    Imo a sp is more flexibe than using sp_executesql.

    /rockmoose


    You must unlearn what You have learnt

  • I think the usage of EXEC or Sp_execute will improve the performance as the SQL Server dont have to findout the type of Command you are entering

     

    --------------------------------

  • I think the usage of EXEC or Sp_execute will improve the performance as the SQL Server dont have to findout the type of Command you are entering

     

    --------------------------------

  • EXEC myStoredProcedure @prm1 = 6, @prm2 = 7

    So Sql Server has to find out that you are executing a stored procedure ?

    EXEC( 'EXEC myStoredProcedure @prm1 = 6, @prm2 = 7' )

    EXEC( 'Select rubbish from blabla' )

    So now Sql Server doesn't have to figure out what you are executing ?

    Ok, if you are using sp_executesql, SQL Server will be able to reuse the execution plans and

    might have the same performance as just using a stored procedure.

    /rockmoose

     


    You must unlearn what You have learnt

  • Thank you. I wanted to be sure before turning these things loose. This proves again that 99% of the time simpler is better.

    Performance is not an issue here due to the size of the tables - 2,118 rows max.

Viewing 6 posts - 1 through 5 (of 5 total)

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