Linking Data to Visio with SQL Server Stored Procedures

Visio Professional has the ability to link to various data sources, and one of the most popular is SQL Server. The DATA\Link Data to Shapes … function will present a UI to choose from a table or view, but does not provide any stored procedures. Therefore, this article present a VBA macro that demonstrates how a stored procedure can be added as a refreshable data recordset in Visio.

This macro uses the uspGetManagerEmployees procedure from the AdventureWorks sample database ( see https://msftdbprodsamples.codeplex.com/releases ). It expects one integer parameter, and returns a list of employees for a given manager’s business entity id. You will need to enter your own Data Source and Initial Catalog values.

Public Sub AddOrRefreshFromStoredProc() 
On Error GoTo errHandler 
Dim dds As Visio.DataRecordset  'The data recordset 
Dim ary() As String         'Array to hold the p key columns 
Dim SQLConnStr As String    'The connection string 
Dim SQLCommStr As String    'The Command string 
Dim datasetName As String   'The dataset name 
            
    SQLConnStr = "Provider=SQLOLEDB.1;" & _ 
        "Integrated Security=SSPI;Persist Security Info=True;" & _ 
        "Initial Catalog=AdventureWorks2014;" & _ 
        "Data Source=ARUBA\SQLExpress2014x8;" & _ 
        "Use Procedure for Prepare=1;" 
    SQLCommStr = "EXEC dbo.uspGetManagerEmployees " 
       
Dim busEntity As Variant 
    busEntity = InputBox("Which business entity to you want to retrieve for?", SQLCommStr, 2) 
    If IsNumeric(busEntity) = False Then 
        MsgBox "You must enter an integer", vbCritical, SQLCommStr 
        GoTo exitHere 
    End If 
    SQLCommStr = SQLCommStr & CStr(busEntity) 
    ary() = Split("BusinessEntityID", ";") 
    datasetName = "uspGetManagerEmployees for " & CStr(busEntity)

    'Check if the recordset exists already 
    For Each dds In Visio.ActiveDocument.DataRecordsets 
        If dds.CommandString = SQLCommStr Then 
            dds.Refresh 
            GoTo exitHere 
        End If 
    Next 
    
    Set dds = Visio.ActiveDocument.DataRecordsets.Add( _ 
           SQLConnStr, SQLCommStr, _ 
           VisDataRecordsetAddOptions.visDataRecordsetDelayQuery, _ 
           datasetName)

    dds.SetPrimaryKey VisPrimaryKeySettings.visKeySingle, ary() 
    dds.Refresh

    Visio.ActiveWindow.Windows.ItemFromID( _ 
        visWinIDExternalData).Visible = True

exitHere: 
    Exit Sub 
errHandler: 
    MsgBox Err.Description 
    Resume exitHere 
End Sub

The macro will simply ask for the stored procedure parameter to be entered:

The stored procedure will then be executed an a data recordset will be added to the document.

This recordset can be refreshed in the normal manner, or by re-running the macro with the same parameter.