Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    8

    Question Unanswered: dynamic use of stored procedure resultset

    Hello,

    I have a stored procedur like this:

    --------------------------------------------
    ALTER PROCEDURE dbo.pdpd_DynamicCall
    @SQLString varchar(4096) = null
    AS

    create TABLE #T1
    ( column_1 varchar(10) ,
    column_2 varchar(100) )

    insert into #T1
    execute ('execute ' + @SQLString )

    select * from #T1
    --------------------------------------------

    The problem is that I want to call different procedures that can give back different columns.
    Therefor I would have to define the table #T1 generically.But I don't know how.
    Can anyone help me on this problem?

    thank you
    Werner

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you do not need the creation of the temporary table.

    ALTER PROCEDURE dbo.pdpd_DynamicCall
    @SQLString varchar(4096) = null
    AS

    execute ('execute ' + @SQLString )

    this solves your problem but this is the most useless SP ever and your going to have stuff like cached execution plans that do not match the query you are executing.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    This sproc is very dangerous. Whoever has rights to execute it has right to execute arbitrary sql scripts, such as "truncate table AllMyCustomers". Do you really want that?

    You are asking for trouble if you put this in a production system.

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by wgerbert
    Hello,

    I have a stored procedur like this:

    --------------------------------------------
    ALTER PROCEDURE dbo.pdpd_DynamicCall
    @SQLString varchar(4096) = null
    AS

    create TABLE #T1
    ( column_1 varchar(10) ,
    column_2 varchar(100) )

    insert into #T1
    execute ('execute ' + @SQLString )

    select * from #T1
    --------------------------------------------

    The problem is that I want to call different procedures that can give back different columns.
    Therefor I would have to define the table #T1 generically.But I don't know how.
    Can anyone help me on this problem?

    thank you
    Werner
    Like jezemine says - dynamic SQL needs to be encapsulated in a very controlled fashion.

    I have a similar situation where our "configurator" actually has column names to define a mapping between Inventory and Sales. Long story.

    We offer the column names on a drop-down list.

    The Stored Procedure that does the implementation is passed column names in various positions. Those column names are a result of a drop-down box (so the can't just formulate their own SQL scripting), and they are of limited size (ie: only big enough for a reasonable column name - like 40 characters).

    For your case; if you had a list of column names, types, and sizes rather than the full SQL statement, you could use them to build your temp table.

    I have never tried passing an array (or collection) as an SP_ parameter, but that would be ideal if you have an unknown number of columns.

    You could also first do some parsing verify that they are valid column names (no spaces or punctuation) to further ensure they aren't passing in DLL commands like "truncate table ...".

    So your Stored Procedure would supply all SQL keywords and restrict any from being passed.
    Last edited by vich; 10-13-06 at 13:45.

  5. #5
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    8
    Thank you all for the detailled help!!
    Especially the security aspect is a part I have to rethink.

    best regards
    Werner

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •