Using Stored Procedures

Creating a Stored Procedure

The Stored Procedures used in this test can be created by following the description below: 

Creating a DatabaseOpen SQL Server Management Studio (SSMS).

1. Connect to the SQL Server instance where you want to create the new database.

2. In the Object Explorer pane, right-click on the Databases folder and choose New Query from the context menu.

3. In the new query window, paste the following code:

USE master;
CREATE DATABASE test; GO

This code tells 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 the "Databases" folder (or press F5). You should see the new 'test' database listed under the "Databases" folder.

Creating Tables with Primary Keys (Spec id and Char id)

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 and locate the 'test' database. Right-click on the 'test' database and choose New Query from the context menu.
3. In the new query window, paste the following code:

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

This code creates two tables, 'dbo.Characteristic' and 'dbo.Specification', with primary 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 tables will be created and populated with the provided data.
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 the Tables folder.

Creating a Third Table That Contains the PrimaryKeys from the Other Two Tables

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 and locate the 'test' database. Right-click on the 'test' database and choose New Query from the context menu.
3. In the new query window, paste the following code:

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

This code 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, 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.Active_Spec' table under the Tables folder and the 'dbo.TestSP' stored procedure under the Programmability → Stored Procedures folder.

Executing Procedure in SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance where the 'test' database and 'TestSP' stored procedure were created.
2. In the Object Explorer pane, expand the Databases folder and locate the 'test' database. Right-click on the 'test' database and choose New Query from the context menu.
3. In the new query window, paste the following code:

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.

Saving the Result of a Query to a Tag or .NET Variable 

Suppose your application has a Tag of type DataTable named "Test" and a Dataset Query named "Query1". You can populate the "Test" Tag by executing the following steps:

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

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 below:

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

Configuring the Project

1. Go to the Datasets → DBs page, and create a new DB for the SQL Server provider. The user must have administrator privileges in order to input the logon name and password.

For this test, the Stored Procedure was created in the Test catalog.

2. On Datasets → Queries, create a new query assigned to this DB connection.

3. In the Draw Environment, a DataGrid was added, and its DataSource 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...