Data Linking to a Stored Procedure

  • Article

Visio allows you to link to data from external data sources so that you can visualize the data on the shapes within your diagrams, and one of the external data sources that Visio supports out of the box is SQL Server.

The Data Linking wizard in Visio 2010 Professional and Premium will allow you to browse to your SQL server instance to select the database and table that you want to link to the diagram.  What you cannot do in this wizard is link to a stored procedure, even though the Visio client and Visio Services are able to execute a stored procedure, so let me show you how easy it is to link your diagram to the results from a stored procedure in SQL.

Create the DataRecordSet

The Data Linking wizard, when complete, creates a new datarecordset in the diagram that matches the schema of the linked table.  But because the wizard will not let you link to a stored procedure you will need to first create a table that will match the schema of the result set from your stored procedure.

For this example I just created a simple table with the fields as shown here:

image

Once the schema for the table was finished I populated some sample data into the table for testing purposes.

image

The next step was to simply run the Data Linking wizard in Visio 2010 Premium to create a datarecordset in my diagram from this temporary table, as shown here:

image

As you can see the columns from the schema have all been added and the sample data from the table was imported to the datarecordset.

Your Stored Procedure

Before you go about changing the connection to the table to execute your stored procedure make sure the results conform to the same schema that was just used to create the datarecordset.  If the schema does not match you will have unexpected results refreshing the data in the diagram.  For example:

- You cannot change the column names that are returned in your result set that is returned from your stored procedure

- You cannot change the data type for the columns in your result set

- Columns cannot be missing from the result set.

Bottom line, return exactly the same schema from your stored procedure as the table you originally linked to.

For this article here is the TSQL that I used:

USE [ParamsFromVisio]
GO
/****** Object:  StoredProcedure [dbo].[uspGetSampleData]    Script Date: 02/10/2011 10:08:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Chris Hopkins
-- Create date: 02/10/2011
-- Description:    Returns Data
-- =============================================
ALTER PROCEDURE [dbo].[uspGetSampleData]
    -- Add the parameters for the stored procedure here
    @LastName nvarchar(50) = NULL,
    @FirstName nvarchar(50) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    -- DECLARE @LastRefresh datetime = GETDATE()
    DECLARE @LastRefresh datetime = GETDATE()

    IF @LastName IS NULL AND @FirstName IS NULL
    BEGIN
        SELECT ID, FirstName, LastName, @LastRefresh AS [LastRefresh]
        FROM ParamsFromVisio.dbo.SampleData
    END
    ELSE
    BEGIN
        SELECT ID, FirstName, LastName, @LastRefresh AS [LastRefresh]
        FROM ParamsFromVisio.dbo.SampleData
        WHERE FirstName = @FirstName AND LastName = @LastName;   
    END
   
END

Executing this stored procedure gives me the results that I was expecting

image

Alter the CommandString property

Now for the fun part, altering the CommandString property for the datarecordset object in the Visio diagram to redirect it from the standard ‘SELECT * FROM…’ type of CommandString to instead execute our stored procedure.

VBA is built into the product so we can easily use a macro to alter this…

Public Sub CommandStringB()

    ' no params sample
    ThisDocument.DataRecordsets(1).CommandString = "exec dbo.uspGetSampleData"

End Sub

It is that easy.  Now save your diagram and click on the Refresh button on the Data table and Visio will execute the stored procedure.

image

You can even add parameters as you would expect in the VBA macro to set the CommandString…

Public Sub CommandStringC()

    ' pass params
    ThisDocument.DataRecordsets(1).CommandString = "exec dbo.uspGetSampleData 'Bonell', 'John'"

End Sub

The final step of course is to publish this diagram to your SharePoint 2010 document library so that Visio Services will refresh the diagram in the browser from your stored procedure.

image