Automating Stored Procedures

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name. It is stored in the database so it can be easily shared by a number of programs. There are numerous advantages to using stored procedures. First, stored procedures can be helpful in controlling access to data. Users can manipulate data, but only in the ways specified. 

Stored procedures also allow you to achive consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

Finally, stored procedures improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied—all of the work is done on the server.

This article shows you how to use stored procedures with Automate. 

Database Table Setup

Before we can dive into the Automate task creation, we have to make sure that the database side is configured properly. (The measure twice, cut once approach is preferable. We are not fans of the cut once…spend-rest-of-the-afternoon-debugging approach.)

For this demonstration, we're using Microsoft SQL Server 2008 R2. It is important to note that this process will work exactly the same with other database management systems such as Oracle and IBM’s Informix. As you can see below, we've created a Sample Database with a new table called Employees. This table has six columns: ID, FirstName, LastName, EmployeeID, Status, and SecurityCode.

 

Once the table has been created, we need to populate it with some information. You can manually enter it into the table or execute a couple of INSERT INTO statements. I have 10 entries in my table, but you can have as few or as many as you want.

 

Create Stored Procedure

So now that the table is set up, we need to create a new Stored Procedure. In SQL Server, we expand the Programmability folder and right click on the New Stored Procedures.

 

Once you have done that, this screen should appear:

Replace the template text in Figure 4, and paste in the following text:

USE [Sample]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:                     Network Automation

-- Create date:

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[NetAutoGetEmployee]

            -- Add the parameters for the stored procedure here

            @empID int,

            @var_return varchar(100) OUTPUT

AS

BEGIN

            -- SET NOCOUNT ON added to prevent extra result sets from

            -- interfering with SELECT statements.

            SET NOCOUNT ON;

     -- Insert statements for procedure here

    SET @var_return = (SELECT [FirstName]

  FROM [Sample].[dbo].[Employees]

  WHERE  [ID] = @empID)

END

Once you have done that, hit the Execute button on the toolbar. If you have done everything correctly, you should see a successful completion in your message output. Hit refresh and you should now see the stored procedure inside the Stored Procedure folder. What this simple Stored Procedure does is take in a value for the ID number (@empID). It then queries the first name that matches that ID and returns that value in the variable @var_return. 

Now right click on the Stored Procedure and select the Execute Stored Procedure.

 

On the next screen, input whatever value you want for the @empID value. We're using 2. Then click OK.

 

From that, you should receive a results output. In this case the value contained within @val_return is HEATHER.

 

Automate’s Interactions with Stored Procedures

So now our database table is set up and our stored procedure is done. The next thing we are going to look at is how Automate interacts with this stored procedure. The first thing we are going to do is open the Task Builder.

 

Now we’re going to create two variables: one is to store the input value (of 2) and the other the output value which we’ve left blank. (This is done by dragging out the Create Variable Action and specifying the name/initial value.) 

 

 

Now we are going to use the Stored Procedure Action. (This can be found in the Database Folder > Stored Procedure). Drag that into the workspace.

 

From here we are going to create the connection string. Click on the little white page to the right of the Connection String Textbox. You then need to specify the Provider/Connection Information. Since I’m using SQL Server, I need to select Microsoft OLE DB Provider for SQL Server. Then click next.

 

Next you need to specify the server name (from the drop down), Log-in Credentials and the Database Server. You can click “Test Connection” to verify that the information is correct. Then click OK.

 

Then you need to specify what stored procedure you wish to use from the drop down menu. For this demonstration we are going to select the one we created earlier (NetAutoGetEmployee).

 

Once I select the stored procedure, it automatically populates the Parameter list with the specified variables.

 

Now we need to edit the values for a couple of these parameters. For the @empID value we need to pass in the input variable we created. Select the @empID parameter and click edit. In the Value text box, click on the “%” to open the expression builder.

 

Select the VAR_Input Variable that you created earlier and click insert.

 

Then click OK. 

 

Now that you have specified the Input value, we need to assign a variable to the @var_return. So we want to select the @var_return parameter and click edit. This time, rather than specifying it in the Values text box, you want to select it from the “Put the result in this variable” dropdown. Select VAR_Output and click OK.

 

Then click OK. You should now have three steps:

 

The last thing we are going to do is display the returned value in a pop-up dialog box. Find the Window folder and select the Dialog action. In the Message to Display box use the Expression Builder (%) to again select the output Variable. Then click OK.

 

Congratulations, you are now an Automate stored procedures expert! At this point the requisite celebration involves printing out this article, sticking a gold star at the top, and proudly displaying it at your desk. Please note that this may lead to an increase in envious glares from coworkers.