Automating Stored Procedures

by Jonathan Chan, in Ask an ASE, posted 1/5/12
image for article

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 in complied form so that it can be easily shared by a number of programs. There are numerous advantages to using Stored Procedures:

  • Stored Procedures can be helpful in controlling access to data (allowing users to manipulate data, but on a controlled, specified manner).
  • Stored procedures assist in achieving a 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.
  • Stored procedures can also 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.

As you may have guessed by now, we are covering the use of Stored Procedures in our Ask an ASE blog this month. Exciting!

Database Table Setup

Before we can dive into the AutoMate task creation part, 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, I’m using Microsoft SQL Server 2008 R2. (It is important to note that this process will work exactly the same with other DBMS such as Oracle and IBM’s Informix.) As you can see below, I’ve created a Sample Database with a new table called Employees. This table has six columns: ID, FirstName, LastName, EmployeeID, Status, and SecurityCode (Figure 1).

Figure 1

Figure 1

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 (Figure 2).

Figure 2

Figure 2

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 (Figure 3).

Figure 3

Figure 3

Once you have done that, this screen should appear:

Figure 4

Figure 4

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. Further information regarding the syntax of Stored Procedures can be found on the MSDN website (Figure 5).

Figure 5

Figure 5

Now right click on the Stored Procedure and select the Execute Stored Procedure (Figure 6).

Figure 6

Figure 6

On the next screen, input whatever value you want for the @empID value. I’m using 2. Then click OK (Figure 7).

Figure 7

Figure 7

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

Figure 8

Figure 8

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 (Figure 9).

Figure 9

Figure 9

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.) See Figure 10 and 11.

Figure 10

Figure 10

 

Figure 11

Figure 11

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 (Figure 12).

Figure 12

Figure 12

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 (Figure 13).

Figure 13

Figure 13

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 (Figure 14).

Figure 14 

Figure 14

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). See Figure 15.

Figure 15

Figure 15

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

Figure 16

Figure 16

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.

Figure 17

Figure 17

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

Figure 18

Figure 18

Then click OK.

Figure 19

Figure 19

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 (Figure 20).

Figure 20

Figure 20

Then click OK. You should now have three steps:

Figure 21

Figure 21

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 (Figure 22).

Figure 22

Figure 22

Now select the Run button. If everything was done correctly, you should see this pop-up (Figure 23). The contents of the pop-up should contain the first name of the user ID that you passed in. In this case, it is HEATHER again.

Figure 23

Figure 23

Congratulations, you are now an AutoMate Stored Procedure expert! At this point the requisite celebration involves printing out this blog post, 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.

*Network Automation is not responsible to any office shenanigans that may result.

We have lots of interesting AutoMate projects lined up for 2012, so stay tuned.

                                                       * * * 

Have an idea for the next Ask an ASE blog post? Send them to askanase@networkautomation.com