Post reply

Return Dynamic Sql results into a Table (How to)

  • January 23, 2008 at 10:13 am

    #71064

    I think I know the 2-letter answer i might get on this one, but here goes ....

    (1) create a dynamic query consisting of some fixed, and some user-selected columns.

    i.e. user is given a choice of 10 columns he may choose to include/exclude.

    (2) create a table containing the resultant records, that may be used in same/other sp for subsequent processing.

    Part (1) is fine, I can happily create the query and execute sql String with exec or sp_executesql.

    However, Part(2) is proving difficult !

    .....to my knowledge, i cannot execute an "select * into .." using exec/sp_execute commands and return an inscope table.

    The only way I have managed, is to pass the users column names into a caller sp where they are used to dynamically create a table, then invoke a callee sp to populate it.

    Now I have a table, but as its created dynamicaly, it is not a temp table and therefore must be named uniquely for each user.

    ... I could carry on, but as you can see, this is just becoming desperate and not exactly neat, maintainable or comfortable !

    Is there ANY way, I can execute a dynamic sql statement, and return the recorsdet into a table within the scope of current sp, preferably without having to declare the table's column names beforehand ?

    For exmple ...

    "Select * into #temp from exec(myDynamicSql)"

    or

    exec ('select * into #temp from ' + @sqlString)

    Hope there's a solution out there somewhere ???

    Thanks in advance !!

  • January 23, 2008 at 2:26 pm

    #771059

    Sim

    To my knowledge SQL 2000 does not support

    either

    "Select * into #temp from exec(myDynamicSql)"

    or

    exec ('select * into #temp from ' + @sqlString)

    But it supports

    "Insert Into #tmp

    exec (@myDynamicSql)".

    However the condition that you preferred

    "preferably without having to declare the table's column names beforehand" cannot be satisfied, as "Insert Into" requires the table #tmp be created before used.

    I also look forward to hearing from somebody if he/she could resolve your request.

    Regards

  • January 23, 2008 at 2:29 pm

    #771064

    I don't see what's your problem.

    As soon as you allow users to shift the columns you don't care about datatypes. They all gonna be displayed as strings anyway.

    Then nothing stops you from creating #table with 10 nvarchar(200) columns which to be populated with your dynamic SQL.

    It could be even sql_variant columns, if you need to modify the data before displaying it ti user.

    If you build SQL in stored procedure then you know number of columns and can build #table correspondingly.

    If you send whole SQL string from UI (prepare to be hijacked!) you can live with fixed number of 10 columns in #table.

    Just when you build dynamic SQL take care of dummy columns to make total number always 10.

    In UI you can cut the columns with "dummy" names off the recordset.

    _____________
    Code for TallyGenerator

  • January 23, 2008 at 2:35 pm

    #771067

    You can use global temp table ##temp. However you will need to delete it after you are done, otherwise it will stay in tempdb.

  • January 23, 2008 at 4:42 pm

    #771103

    Loner (1/23/2008)

    You can use global temp table ##temp. However you will need to delete it after you are done, otherwise it will stay in tempdb.

    Problem is OP needs

    a table within the scope of current sp.

    ##Table obviously does not work here.

    _____________
    Code for TallyGenerator

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

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