This article will give you information about what Stored Procedures are, and provide examples about how to use them. If your Data Base Administrator (DBA) has set up a stored procedure in your SQL data source, you will have access to it through the Data Tree and the Tag Editor in AutoTag. Windward's Stored Procedure Wizard is available to help those who may need help using a stored procedure.
What is a Stored Procedure?
With AutoTag, the tags help you create that select statement and take care of contacting your data source when you Output. When you or your DBA create a query or select statement for an SQL database, it is executed on the server and sends a response with the returned data.
If you frequently use the same question(query) then you can save it as a View. This is similar to setting a schedule for your DVR to record your favorite show at the same time each week.
But what if you want to change or filter the data returned each time you ask the question(query)? This is where you can make use of a stored procedure. A stored procedure is basically a View where you can enter values to change or filter the returned data. So if you wanted to change the show that you record on your DVR each week, you could use a stored procedure which would set up a schedule to record the show but ask you each time which show you wanted to record. A stored procedure needs a variable or parameter to run; the selection of the show is the variable or parameter that a stored procedure needs to operate correctly.
Where do I use this?
Stored procedures are useful when you need data in your report, but that data varies each time the report is run. Since a stored procedure allows you to enter values to change the SQL query each time it is run, you can retrieve exactly the data you need each time. Anywhere you would normally ask someone for additional information when completing the same task repeatedly would be a good use for a stored procedure.
Common uses are:
- order numbers
- data ranges
- customer ID's
- category filters
If your stored procedure returns more than one column of data, you need to use a ForEach Tag to display multiple columns.
If your stored procedure returns only a single value, then use an Out Tag.
How to Use the Stored Procedure Wizard
When you select a stored procedure in AutoTag from the Data Tree in the MS Ribbon menu and that stored procedure contains parameters, you will then be prompted with this wizard.
Download the sample file here: Stored_Procedure_Wizard_Example.docx
For directions on Connecting to the Windward sample MSSQL data source, click here.
Step 1: Setup
In our example below we are using a stored procedure named CustOrderHist(@CustomerID).
Let's break this down.
The name of the stored procedure is CustOrderHist and the parameter(variable) it needs to operate is @CustomerID. If the stored procedure CustOrderHist is run, it returns the columns, Product Name and Total.
Create a 2x2 table in your open Microsoft Word template DOCX file
A) Enter Product Name in row 1, column 1. Enter Total in row 1, column 2
B) Insert an empty ForEach and Out Tags in row 2, column 1 of your table
C) Insert an empty Out Tag in row 2, column 2 of your table
D) Insert an End ForEach Tag
E) Style your table and update the tag nicknames as you like
Step 2: Build the stored procedure statement
There are 2 ways to setup stored procedures in AutoTag. You can select a tag and choose one from the Data Tree and set the value with the wizard. You can also manually select a stored procedure in the Tag Editor and create the value by manually entering it. Both processes are shown below.
Wizard Value Entry
1. Select the Stored Procedure in the Data Tree
Stored procedures are not displayed in the Data Bin. In order to use a stored procedure, you need to follow the steps below to select it from the Data Tree.
A) Select or insert the tag that you'd like to use with your stored procedure in the template.
B) With the tag selected, click on the Data Tree in the AutoTag Ribbon Tag Properties area.
C) From the Data Tree dropdown, expand your data source and the stored procedures node and select the Stored Procedure. For this example, choose CustOrderHist(@CustomerID).
|Version 14 and Earlier||Version 15 and Later|
2. Assign a data type and value for each stored procedure parameter
For a stored procedure to work properly you need to specify the type of data you send it as a parameter. The wizard analyzes your stored procedure and then lists all parameters(variables) as a new row in the stored procedure wizard window. The wizard automatically detects the type of data that the parameter needs, however you can change the data type manually in the wizard. Lastly a value needs to be assigned to the parameter. This is what will be replaced in the SQL query of the stored procedure when it is executed.
A) Check to make sure the Parameter is set to @CustomerID
B) Check to make sure the Type is set to StringFixedLength
C) Change the Value to ALFKI
For a full explanation of each stored procedure wizard column, see the specifications below.
Manual Value Entry using the Tag Editor
This method uses the Tag Editor.
A) Select the ForEach Tag and open the Tag Editor, you will see the different stored procedures available listed in the left pane.
B) Drag the stored procedure CustOrderHist(@CustomerID) from the Data Pane to the Query pane, it will appear as shown below:
exec CustOrderHist @CustomerID = S'0'
C) Replace the parameter "0" in S'0' with the value ALFKI. Delete the value 0 and enter ALFKI between the single quotes. Your query should appear as shown below.
exec CustOrderHist @CustomerID = S'ALFKI'
D) Selecting the Preview button will run the stored procedure with the parameter ALFKI and return a table of Product Names and Totals for the customer with ID ALFKI.
E) Save your ForEach Tag and close.
Step 3: Output the template.
You report should appear like the one below.
Stored Procedure Wizard Specifications
The first column is the name of each parameter. This information is retrieved from the database, it identifies the data you need to send to the stored procedure to run properly.
The second column is the data type of the parameter. This information is also retrieved from the database, it identifies the form of the data. For example; if it is a date, it needs to be in the form 2011-07-23 and if it is an integer, it needs to be in the form 123 (no decimal).
The third column is the data to send to the stored procedures for that parameter. You can enter a value by directly typing it. You can also click the drop down button on the right and it will drop down a tree of all variables available in the template. Select one to have that variable value sent to the stored procedure as the parameter when the template is run.