Post reply

Dynamic SQL within Stored Proc without using sp_executesql

  • March 4, 2004 at 1:46 pm

    #107322

    Does anyone know how to execute a dynamically generated sql statement within a stored procedure without using the sp_executesql procedure?  We have a need to pass from the user 15 different parameters and 4 different grouping options which can be a value or can be "not applicable". 

  • March 4, 2004 at 2:37 pm

    #497403

    What's wrong with sp_executesql?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 4, 2004 at 2:40 pm

    #497404

    My DBA doesn't want me to use it because of  performance and security concerns.

  • March 4, 2004 at 2:43 pm

    #497405

    can't you use a view ?


    * Noel

  • March 4, 2004 at 2:45 pm

    #497407

    How would a view help? 

  • March 4, 2004 at 2:53 pm

    #497411

    With a view

    1. you don't have to use dynamic sql.

    2. you don't have to grant direct access to the tables

    3. you can create the statements on the client against the view.


    * Noel

  • March 4, 2004 at 2:55 pm

    #497415

    We are required to access the database through stored procedures.  So the client couldn't just select against a view in our situation.  Thanks for the idea though

  • March 4, 2004 at 2:55 pm

    #497414

    Hey, your DBA is a wise man.

    Actually you can use far more than 15 parameters in an s_proc and for the grouping options there is also another solution. I'm sure you'll find something useful here. I rechecked the article today because of a foolish answer in another thread.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 4, 2004 at 3:07 pm

    #497419

    ok, if that is your only option :

    1. READ the article that Frank posted!

    2. besides trying to do all in one procedure with one single query sometimes it makes sense to write several stored procedures that are called from the main one depending on the input conditions

    3. consider the use of temp tables it may help with the dynamic sql and security issues

    HTH

     

     


    * Noel

  • March 4, 2004 at 3:13 pm

    #497423

    Thanks Frank, that article has some great ideas!

Viewing 10 posts - 1 through 9 (of 9 total)

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