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:
Once the schema for the table was finished I populated some sample data into the table for testing purposes.
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:
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
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.
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.
