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.
   
   SELECT * INTO _tmp FROM ( some sub query ) t

   1	SELECT * INTO _tmp FROM ( some sub query ) t

2. Once you have data we can use it few ways to get datatypes. Lets create a view to query metadata
   
   Transact-SQL

   CREATE VIEW dbo.vwDataTypes AS SELECT c.name as ColumnName, t.name + CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '('+ CASE WHEN c.max_length=-1 THEN 'MAX' ELSE CONVERT(VARCHAR(4), CASE WHEN t.name IN ('nchar','nvarchar') THEN c.max_length/2 ELSE c.max_length END ) END +')' WHEN t.name IN ('decimal','numeric') THEN '('+ CONVERT(VARCHAR(4),c.precision)+',' + CONVERT(VARCHAR(4),c.Scale)+')' ELSE '' END as FriendlyDataType, t.name as DataType, c.[object_id] as [TblObjectId], c.max_length MaxLengthBytes, c.precision as [Precision], c.scale as [Scale], c.is_nullable as IsNullable, ISNULL(i.is_primary_key, 0) as IsPrimaryKey FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id --WHERE c.object_id = OBJECT_ID('_tmp') go

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

   CREATE VIEW dbo.vwDataTypes AS SELECT    c.name as ColumnName,    t.name +    CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '('+              CASE WHEN c.max_length=-1 THEN 'MAX'                   ELSE CONVERT(VARCHAR(4),                                CASE WHEN t.name IN ('nchar','nvarchar')                                THEN  c.max_length/2 ELSE c.max_length END )                   END +')'           WHEN t.name IN ('decimal','numeric')                   THEN '('+ CONVERT(VARCHAR(4),c.precision)+','                           + CONVERT(VARCHAR(4),c.Scale)+')'                   ELSE '' END       as FriendlyDataType,    t.name as DataType,    c.[object_id] as [TblObjectId],    c.max_length MaxLengthBytes,    c.precision as [Precision],    c.scale  as [Scale],    c.is_nullable as IsNullable,    ISNULL(i.is_primary_key, 0) as IsPrimaryKey FROM        sys.columns c INNER JOIN    sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id --WHERE    c.object_id = OBJECT_ID('_tmp') go

3. Now you can write below query to generate Comma separated list of columns names and datatypes
   
   DECLARE @csv VARCHAR(MAX) SELECT @csv = COALESCE(@csv+', ' ,'') + '[' + ColumnName + '] ' + FriendlyDataType FROM vwDataTypes WHERE [TblObjectId]= OBJECT_ID('_tmp') SELECT @csv

   1 2 3 4

   DECLARE @csv VARCHAR(MAX) SELECT @csv = COALESCE(@csv+', ' ,'') + '[' + ColumnName + '] ' + FriendlyDataType FROM vwDataTypes WHERE [TblObjectId]= OBJECT_ID('_tmp') SELECT @csv

   Example : 
   
   [CustomerID] nvarchar(10), [CustomerName] nvarchar(68), ......... [Freight] nvarchar(16)

   1	[CustomerID] nvarchar(10), [CustomerName] nvarchar(68), ......... [Freight] nvarchar(16)

4. Use result of above query and paste in WITH RESULT SETS as below
   
   EXECUTE( @sqlFull ) WITH RESULT SETS (   (  your-comma-separated-list-goes-here ) )

   1 2 3 4 5

   EXECUTE( @sqlFull ) WITH RESULT SETS (   (  your-comma-separated-list-goes-here ) )