Skip to main content
Windward

Using SQL Stored Procedures

Overview

Explanation of store procedure syntax used in AutoTag.  How to reference multiple parameters in a stored procedure.

Resolution

This link gives information on how to use the Windward stored procedure wizard to generate a stored procedure query in our tag's selection window.

http://wiki.windward.net/Wiki/03.AutoTag/05.AutoTag_User_Guide/03.Tagging/09.Tag_Wizards/The_Stored_Procedure_Wizard

 

The attached example Stored Procedure Example.docx uses Windward's public MS SQL database with these credentials:

 

Server:         mssql.windward.net

Database:    Northwind

Username:   demo

Password:   demo

 

The example uses the CustOrdersDetail stored procedure in the Northwind database.

 

Below are the steps for setting up a forEach loop to iterate through the values returned by this stored procedure:

  1. To insert a forEach tag into your template, click on the AutoTag tab then click the Tags button and select forEach

 

SP1.JPG

 

  1. Select the forEach tag you just inserted and on the AutoTag tab, click the Data Tree button then select the CustOrdersDetail Stored Procedure:

SP2.JPG

 

  1. The Stored Procedure Wizard will appear.  This lists the parameter (s) requested by the stored procedure - in this case, we need to provide and Order ID.  In order for the stored procedure to run you must enter a static value or variable reference - we will cover both options in the next two steps:

SP3.JPG

 

4. Inserting a static value - To insert a static value, simply type the value into the Value field in the Stored Procedure Wizard.  In order to return data, the static value must be a value that exists in the data source.  In this example we are entering an OrderID of 10249.

SP4.JPG

 

Here is the syntax for entering a static value:

exec StoredProcedureName @ParameterName = i'StaticValue'

Example:

exec CustOrdersDetail @OrderID = i'10249

 

  1. Using a variable reference - If you have created the variable, you will need to do that first. See the Defined Variables article. In the Stored Procedure wizard use the pull down menu under Value to select your variable

SP5.JPG

 

Here is the syntax for entering a variable reference:

exec StoredProcedureName @ParameterName = ${variable}

Example:

exec CustOrdersDetail @OrderID =${order}

 

Syntax note for multiple parameters:

If your stored procedure has multiple values then the stored procedure call will take this form:

 

exec StoredProcedureName @ParameterName1 = value1, @ParameterName2 = Value2

 

exec [Employee Sales by Country] @Beginning_Date = D'0', @Ending_Date = D'0'

 

 

Values can be static or dynamically referenced via a variable. 

 

Each @ParameterName = Value statement needs to be separated by a comma.

Now that your forEach is set up with your stored procedure using a static value or variable reference, you can now set up your Out tags with the data from the stored procedure that you want to print to output.  

  1. To insert an Out tag, click on the AutoTag tab then click the Tags button and select Out:

SP6.JPGhttps://wiki.windward.net/@api/deki/files/1500/Stored_Procedure_Example.docx

 

  1. With the Out tag selected in your template, use the Data Tree to select the data items from the forEach - for more information on selecting data from your forEach loop see Inserting Tags within a ForEach Tag:

SP7.JPG

 

Continue entering Out tags and selecting data until all the data you need is in your template.