Resolving Dynamic SQL Issues in Dashboard Creation with Open Query

When creating a new dashboard using open query, you may encounter an issue if you are using dynamic SQL in your procedure. This issue can be resolved by either using the internal-cache option in Bold BI or modifying the procedure WITH RESULT SETS.

Using Internal-Cache Option in Bold BI

Bold BI provides an internal-cache option that can be used as a workaround for this issue. This option allows you to store and retrieve data without having to execute the SQL query every time.

Modifying the Procedure WITH RESULT SETS

Another solution is to modify the procedure WITH RESULT SETS. Here is an example of how you can modify your procedure:

Before:

CREATE PROC GetCount (
    @table NVARCHAR(128)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    -- construct SQL
    SET @sql = N'SELECT count(*) FROM ' + @table;
    -- execute the SQL
    EXEC(@sql)
END;

After:

CREATE PROC GetCount (
    @table NVARCHAR(128)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    -- construct SQL
    SET @sql = N'SELECT count(*) FROM ' + @table;
    -- execute the SQL
    EXEC(@sql) WITH RESULT SETS
(
  (
    val SMALLINT
  )
);
END;

References

• SQL Server Stored Procedures
• Dynamic SQL