Stored Procedures

Creating a Stored Procedure

The procedure to create a Stored Procedure is summarized by the following steps:

1. Create a Database in the Database management system.
2. In this Database, Create Tables with primary keys.
3. Create a third Table containing these primary keys from the other Databases.
4. Execute the Procedure in the Database management system.
5. Save the result of the Query to a tag or .Net variable.
6. Execute the Stored Procedures.

The following sections address each of the above steps in detail.

Step 1: Create a Database

First, you need to create a Database using a management system. This example uses the SQL Server Management Studio (SSMS). Follow the steps below to create a database using SQL Server Management Studio:

1. Connect to the SQL Server instance where you want to create the new database.
2. In the Object Explorer panel, right-click Databases and choose New Query.

3. In the New Query window, paste the code below.  It tells the SQL Server to switch to the master database and then create a new database called test.
   

   USE master;
   CREATE DATABASE test; GO

   
   

4. Click the Execute button or pressing F5 to run the Query. As a result, a new test database is created.

5. In the Object Explorer panel, click the Refresh button next to Databases or pressing F5. You should see the new test Database listed.

Step 2: Create Tables with primary keys

After creating the test Database, you need to create a Table with the primary keys (Spec Id and Char Id). Follow the steps below.

1. Open SQL Server Management Studio and connect to the SQL Server instance where you created the test Database.
2. In the Object Explorer panel, expand the Databases folder, right-click on the test Database, and choose New Query.

3. In the New Query window, paste the code below to create two tables, dbo.Characteristic and dbo.Specification , with the primary keys Char_Id  and Spec_ID , respectively. It also inserts sample data into both tables.

   USE test GO
   CREATE TABLE dbo.Characteristic (Char_Id int PRIMARY KEY NOT NULL,
   Char_Desc varchar(25) NOT NULL, OptionsChar varchar(25))
   GO
   INSERT dbo.Characteristic (Char_Id, Char_Desc, OptionsChar) VALUES(10,’C010’,’Char1’),(15,’C015’,’Char2’),(19,’C019’,’Char3’),(14,’C014’,’Char4’),(18,’C018’,’Char5’),(17,’C017’,’Char6’),(16,’C016’,’Char7’)GO
   CREATE TABLE dbo.Specification (Spec_ID int PRIMARY KEY NOT NULL,
   Spec_Desc varchar(25) NOT NULL, OptionsSpec varchar(25))
   GO
   INSERT dbo.Specification (Spec_ID, Spec_Desc, OptionsSpec) VALUES
   (30, ’Spec 30’, ’Spec1’),
   (32, ’Spec 32’, ’Spec2’),
   (33, ’Spec 33’, ’Spec3’),
   (37, ’Spec 37’, ’Spec4’),
   (34, ’Spec 34’, ’Spec5’),
   (39, ’Spec 39’, ’Spec6’),
   (35, ’Spec 35’, ’Spec7’) GO

4. Click the Execute button or pressing ing F5 to run the Query. The new tables will be created.
5. In the Object Explorer panel, refresh the test database by right-clicking it and selecting Refresh or pressing F5. If you expand the test database, you should see the newly created dbo.Characteristic and dbo.Specification  tables under Tables.

Step 3: Create a Table containing the primary keys from the other Tables

The next step is to create a third table containing the primary keys from the previously created tables used to create the Store Procedure. Follow the next steps to create the new table:

1. Open SQL Server Management Studio and connect to the SQL Server instance where the test database was created.
2. In the Object Explorer panel, expand the Databases folder, right-click on the test Database, and choose New Query.

3. In the New Query window, paste the code below. As a result, the system will create a new Table dbo.Active_Spec  containing the Primary Keys from the other two Tables dbo.Characteristic and dbo.Specification.It also inserts sample data into the dbo.Active_Spec table and creates a Stored Procedure named dbo.TestSP .

   CREATE TABLE dbo.Active_Spec (AS_ID int PRIMARY KEY NOT NULL,
   Char_ID int NOT NULL, Spec_ID int NOT NULL, OptionsAS varchar(25))
   GO
   INSERT dbo.Active_Spec(AS_ID, Spec_ID, Char_ID, OptionsAS) VALUES(1,30,10,’AS1’),(2,37,19,’AS2’),(3,34,19,’AS3’),(7,35,16,’AS7’),(4,34,19,’AS4’),(6,39,18,’AS6’),(5,32,19,’AS5’)GO
   USE [Test] GO
   SET ANSI_NULLS ON GO
   SET QUOTED_IDENTIFIER ON GO
   CREATE PROCEDURE [dbo].[TestSP]
   @Char_Desc varChar(50)
   AS BEGIN
   SET NOCOUNT ON;
   
   Declare @Char_Id int, @Spec_Id int
   
   Select @Char_Id = Char_Id from Characteristic where Char_Desc = @Char_Desc
   
   Select c.char_Desc, s.Spec_Id, s.Spec_Desc, c.OptionsChar, s.OptionsSpec, asp.OptionsAS
   From Specification s
   Join Active_Spec asp on asp.Spec_Id = s.Spec_Id Join Characteristic c on c.Char_Id = asp.Char_Id
   Where c.Char_Id = @Char_Id GROUP BY c.Char_Desc,
   s.Spec_Id, s.Spec_Desc, c.OptionsChar, s.OptionsSpec, asp.OptionsAS
   END

   
   

4. Click the Execute button or pressing F5 to run the query. The table will be created and populated with the provided data, and the Stored Procedure will be added.

5. In the Object Explorer panel, right-click the test Database, select Refresh, or press F5. . Expand the test Database, and you should see the newly created dbo.Active_Spec table under Tables. You find the dbo.TestSP  Stored Procedure under the Programmability → Stored Procedures folder.

Step 4: Execute the Procedure

At this stage, you have created a Stored Procedure and can now execute it. To execute the Procedure recently created, follow the steps below:

1. Open SQL Server Management Studio and connect to the SQL Server instance where the test database was created.
2. In the Object Explorer panel, expand the Databases folder, right-click on the test Database, and choose New Query.

3. In the New Query window, paste the code below, which will execute the TestSP stored procedure with the parameter value C019 .

   EXEC TestSP ’C019’

   
   

4. Click the Execute button or press F5 to run the Query. The stored procedure will be executed, and the results will be displayed in the Results panel, below the query window. The below image shows an example of the query result.
   

Step 5: Save the result of a query

If your application has a Tag of the type Data Table named Test and a Dataset Query named Query1. You can populate the Test Tag using the following code:

@Tag.Test = @Dataset.Query.Query1.SelectComand()

Step 6: Executing Stored Procedures

Both Queries and Stored Procedures are defined in the Datasets → Queries table. To execute a Stored Procedure, use the ExecuteCommand() method. For example,  Dataset.Queries.Query1.ExecuteCommand().


When passing parameters, you can use the @null@ syntax to pass a null value as a parameter, as in the below example:

Exec TestOutput @return_Value = {Tag.ReturnValue} RETURN_VALUE, @vcrPrefix = @null@, @intNextNumber = {Tag.NextNumber} OUTPUT, @vcrFullLicense = {Tag.NextLicense} OUTPUT 

Configuring the Solution

To configure and use Stored Procedures in the solution, follow the steps below:

1. Go to the Datasets → DBs page, and create a new DB for the SQL Server provider.
2. On Datasets → Queries, create a new query assigned to this DB connection.

3. A DataGrid is added to the Draw Environment. Its Data Source is set to the Query created in the previous step.

4. A button is configured to run a Script using the code presented below:

   public void RunSP_Btn(object sender, System.Windows.Input.InputEventArgs e)
   {
   string sqlStatement = string.Format("exec TestSP ’{0}’", "C019"); @Dataset.Query.Query_SqlServer.SqlStatement = sqlStatement;
   
   string sts = ""; @Dataset.Query.Query_SqlServer.ExecuteCommandWithStatus(out sts);
   }

5. After clicking the button and executing the code, the result should be the same as in SQL Server Management Studio (SSMS).

In this section...