Stored Procedures

Creating a Stored Procedure

Creating a Stored Procedure consists of the following steps:

1. Create a Database in the Database managament 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 managament system.
5. Save the result of the Query to a tag or .Net variable.
6. Execute the Stored Procedures.

The sections below will go over these steps in more detail. 

Step 1: Create A Database With SQL Server Management Studio (SSMS).

First create a Database in the used managament system, for the purpose of this example will be using SQL Server Managemenet studio. Follow the steps below.

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

3. In the New Query window, paste the code below.

   USE master;
   CREATE DATABASE test; GO

   It tells the SQL Server to switch to the 'master' database and then create a new database called 'test'.

4. Click the Execute button (or press F5) to run the query. The new 'test' database will be created.

5. In the Object Explorer pane, click the "Refresh" button next to Databases (or press 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 (SSMS) and connect to the SQL Server instance where you created the 'test' Database.
2. In the Object Explorer pane, expand the Databases folder, right-click on the 'test' Database and choose New Query.

3. In the New Query window, paste the code below.

   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

   It creates two tables, dbo.Characteristic and dbo.Specification , with theprimary keys Char_Id  and Spec_ID , respectively. It also inserts sample data into both tables.

4. Click the Execute button (or press F5) to run the query. The new tables will be created.
5. In the Object Explorer pane, refresh the test database by right-clicking it and selecting Refresh (or press F5). Expand the test database, and 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

Now you need to create a third table that contains the primary keys from the previously created tables. Follow these steps to do so.

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

3. In the New Query window, paste the code below.

   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

   It creates 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 .

4. Click the Execute button (or press F5) to run the query. The table will be created, populated with the provided data, and the stored procedure will be added.

5. In the Object Explorer pane, right-click the test Database and select Refresh (or press F5). Expand the test Database, and you should see the newly created dbo.Active_Spec table under Tables, and the dbo.TestSP  stored procedure under the Programmability → Stored Procedures folder.

Step 4: Execute The Procedure in SQL Server Management Studio (SSMS)

Now execute the procedure we created before. Follow the steps below to do so:

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

3. In the New Query window, paste the code below.

   EXEC TestSP ’C019’

   This code executes the TestSP stored procedure with the parameter value 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 pane below the query window.

Step 5: Save The Result Of A QueryTo A Tag Or .NET Variable 

Suppose your application has a Tag of the type Data Table named "Test" and a Dataset Query named "Query1". You can populate the "Test" Tag with 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. See the example code below.

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

Configuring the Project

To configure and use stored procedures in the project, 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 Data Grid was added in the Draw Environment, and its Data Source was set to the Query created above. Next, a button is used to run the script 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);
   }

4. After executing the code above, the result should be the same as in SQL Server Management Studio (SSMS).

Test Results

In this section...