Tableau Parameters – Fix Metadata / Dynamic SQL Columns Issue

Introduction

Many time you have to use Dynamic SQL in your Stored Procedure but some tools (e.g. Tableau) may reject it because metadata is not returned when sp_describe_first_result_set is called in SQL Prepare phase. You may see error like below in that case.

 

 

Metadata Prepare Call

Error due to Dynamic SQL

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 29]
The metadata could not be determined because statement 'EXECUTE (@sqlFull)' in procedure 'usp_GetInvoicesByCountry' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Here is example Stored Procedure which may throw above error. We will re-write same stored proc in later section.

Example of Dynamic SQL in Stored Procedure

 

How to Fix Metadata error for Dynamic SQL

To fix metadata error due to dynamic SQL you have add WITH RESULT SETS statement after EXECUTE call. This feature was added in SQL 2012 and later. There are two ways to describe Stored Procedure / using Dynamic SQL

Method-1

Add  WITH RESULT SETS clause inside Stored Proc if you are using EXECUTE or sp_Executesql like below.

 

Method-2

Another way to describe resultset is add WITH RESULT SETS statement right after you call Stored proc each time from any client tool (see below). This way you don’t have to touch Source stored Proc. First method is still preferred way.

How to generate column names and field datatypes for WITH RESULT SETS

If you have many columns to describe then it becomes tedious to type 200 columns by hand. Lets see how to make it simple.

  1. First step is you need to load your result into some static table.
  2. Once you have data we can use it few ways to get datatypes. Lets create a view to query metadata
  3. Now you can write below query to generate Comma separated list of columns names and datatypes
    Example : 
  4. Use result of above query and paste in WITH RESULT SETS as below

 

 

Posted in Reporting - Tableau.