Post reply

what is the differences between these two procedures ?in case of execution time ect.. any one can help ?

  • June 5, 2008 at 3:09 am

    #77009

    create proc procname

    as

    begin

    declare @STR varchar(100)

    set @STR='SELECT *FROM tablename'

    execute (@str)

    end

    create proc procname2

    as

    begin

    SELECT *FROM tablename

    end

  • June 5, 2008 at 6:18 am

    #824734

    For Small query as u written in your exapmple, it wont hamper as much but in large query yes. because number of transaction are increesing.

    you can check it by showing the client statistics and execution plan in query analyzer.

    -Satya N.Tailor

  • June 5, 2008 at 7:07 am

    #824775

    Basically its going to be a quite a bit of performance hit as in your stored procedure where you used dynamic sql, the plans are not used its a better option to use sp_executesql if you opt to go for dynamic sql for some reasons of desparation to accomplish some task which you believe cannot be achieved without dynamic sql.

    It is always wise to avoid dynamic sql not only from performance perspective but as from security perspective. There are several articles on dynamic sql and its cons for you to really get in depth view of what I meant in the above statements.

    Prasad Bhogadi
    www.inforaise.com

  • June 5, 2008 at 7:28 am

    #824790

    Thanks for your valuable reply and time..

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

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