none
How to use "WITH RESULT SETS" clause in SQL 2012 for dynamic column names. RRS feed

  • Question

  • Hello,

            I have several SPs which return pivoted result as per user's requirement. So the column names each time are different. 

    exec dbo.sp_test 'product_name', 'pivot_on' 
    WITH RESULT SETS((???))

    so at the place of ??? i need to mention the column names. Now the problem is inside that sp column names are stored in one variable so can i write something like,

    WITH RESULT SETS(( col1 nvarchar(100), @allColumns))  ?

    Please guide me.


    ANK HIT

    Wednesday, July 18, 2012 12:45 PM

Answers

  • I know its messy thing to do but I have to, no choice.............. all they told me is we want SP's result into a temp table!

    You have a choice: be a responsible programmer and tell "them" what is possible and what is not.

    Playing with WITH RESULT SETS will take you nowhere. When you run a query with OPENQUERY, SQL Server needs to know at compile time which columns OPENQUERY returns. In previous version it ran the batch preceded by SET FMTONLY ON to get this information. You intervened and added SET FMTONLY OFF, which means that the procedure was executed twice, which is bad in itself. And in SQL 2012, Microsoft has changed the rules, and you can no longer run the procedure twice to get the structure of the result set - which is a good thing if you ask me.

    So you need to find a different approach. One is to take "their" word as law, and go for a solution in the CLR, and I discuss this in my article.

    Or you need to take a discussion and ask: what do you need the temp table for? In the trade of being a programmer, this is one of our duties. When our client or our manager asks us for things which they thing are simple, it is our obligation to tell them that what they are asking for is impossible or at least very expensive.

    Some more thoughts:

    *  If they want to store the data to have the report persisted, it would be better to persist before pivoting, since data now can be stored relationally.

    *  XML could also be an alternative, although getting a result set from a pivotted result set which has stored been in XML may be painful.

    Ultimately: you need to find out why they want this temp table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 18, 2012 7:11 PM

All replies

  • EXEC('SELECT * INTO #t  FROM sys.objects select * from #t;')
    WITH RESULT SETS UNDEFINED;

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, July 18, 2012 12:49 PM
    Answerer
  • EXEC('SELECT * INTO #t  FROM sys.objects select * from #t;')
    WITH RESULT SETS UNDEFINED;

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Still getting the same error, the syntex is like this,

    SELECT TOP 3 *
    
    FROM OPENQUERY(ServerName,'SET FMTONLY OFF; exec dbo.TestSP ''Product'', ''Pivot_On'' WITH RESULT SETS UNDEFINED')

    error is,

    Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
    The metadata could not be determined 
    because statement 'execute (@query2)' 
    in procedure 'usp_DP_PivotedResults' 
     contains dynamic SQL.  Consider using 
    the WITH RESULT SETS clause to 
    explicitly describe the result set.
    


    ANK HIT

    Wednesday, July 18, 2012 1:04 PM
  • So what are you trying to do really?

    It seems that you have painted yourself into a corner, and your only option is to take your can of paint and walk through the wet paint and start over, this time from the right direction.

    It seems that you are doing some dynamic pivot, and that's all right, but why do you try to run this through OPENQUERY? That is not the right way to do it.

    If you explain from start to end what you want to achieve, we might be able to help you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 18, 2012 1:33 PM
  • See I had the same question, basically this SP will have two parameters, one which will ask for the product and other one will be the pivoted value, now for each product the columns will be the different. I made it correct for them and also i added one more parameter which gives the flexibility of selecting the columns also. Now what they want is to store this SP result into some sort of temp table so I used OPENQUERY since i don't know any other way around, also i can not store it in any physical table since it will create lots of tables.


    In short all i want is to store this SP's result into a temp table

    ANK HIT

    Wednesday, July 18, 2012 3:15 PM
  • And what to "they" want do with that temp table, of which I guess the definition cannot be known until the procedure have executed?

    You could of course run the pivoting procedure from a C# procedure that receives the data into a data set, and creates a table (global temp table) and store the data there. It would still be messy.

    I think you need to talk your client/customer/manager what they are really looking for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 18, 2012 5:27 PM
  • I know its messy thing to do but I have to, no choice.............. all they told me is we want SP's result into a temp table!

    What i am thinking like i pass an output parameter and inside this sp i have one parameter which holds all column names which is @col so what i did is select @output=@col so this will store all the column names with "," into that output parameter and follow this script,

    DECLARE @OUT NVARCHAR(4000);
    EXEC dbo.testSP 'Product', 'Pivot_On', @OUTPUT=@OUT OUTPUT
    --SELECT @OUT
    EXEC('exec DBO.TESTSP '+'''C2'''+', '+'''NUMR'''+' 
    WITH RESULT SETS((RUNNO NVARCHAR(MAX),'+@OUT+'))')

    BUT GETTING AN ERROR,

    (129 row(s) affected)
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ','.
    


    ANK HIT

    Wednesday, July 18, 2012 6:19 PM
  • I know its messy thing to do but I have to, no choice.............. all they told me is we want SP's result into a temp table!

    What i am thinking like i pass an output parameter and inside this sp i have one parameter which holds all column names which is @col so what i did is select @output=@col so this will store all the column names with "," into that output parameter and follow this script,

    DECLARE @OUT NVARCHAR(4000);
    EXEC dbo.testSP 'Product', 'Pivot_On', @OUTPUT=@OUT OUTPUT
    --SELECT @OUT
    EXEC('exec DBO.TESTSP '+'''C2'''+', '+'''NUMR'''+' 
    WITH RESULT SETS((RUNNO NVARCHAR(MAX),'+@OUT+'))')

    BUT GETTING AN ERROR,

    (129 row(s) affected)
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ','.


    ANK HIT

    Only problem in this script I am seeing is that inside that WITH RESULT SETS, we need to assign column name and data type but above script only provides column names , this might help. all columns are NVARCHAR(4000)!

    ANK HIT

    Wednesday, July 18, 2012 6:27 PM
  • I know its messy thing to do but I have to, no choice.............. all they told me is we want SP's result into a temp table!

    You have a choice: be a responsible programmer and tell "them" what is possible and what is not.

    Playing with WITH RESULT SETS will take you nowhere. When you run a query with OPENQUERY, SQL Server needs to know at compile time which columns OPENQUERY returns. In previous version it ran the batch preceded by SET FMTONLY ON to get this information. You intervened and added SET FMTONLY OFF, which means that the procedure was executed twice, which is bad in itself. And in SQL 2012, Microsoft has changed the rules, and you can no longer run the procedure twice to get the structure of the result set - which is a good thing if you ask me.

    So you need to find a different approach. One is to take "their" word as law, and go for a solution in the CLR, and I discuss this in my article.

    Or you need to take a discussion and ask: what do you need the temp table for? In the trade of being a programmer, this is one of our duties. When our client or our manager asks us for things which they thing are simple, it is our obligation to tell them that what they are asking for is impossible or at least very expensive.

    Some more thoughts:

    *  If they want to store the data to have the report persisted, it would be better to persist before pivoting, since data now can be stored relationally.

    *  XML could also be an alternative, although getting a result set from a pivotted result set which has stored been in XML may be painful.

    Ultimately: you need to find out why they want this temp table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 18, 2012 7:11 PM