Versions Compared

Old Version 8

changes.mady.by.user user-8416b

Saved on

compared with

New Version 9

changes.mady.by.user user-8416b

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Overview

A Stored Procedure is a precompiled set of SQL statements stored in a database management system, which can be executed upon request by an application or user request. 

This section will guide you through the process of creating, configuring, and executing stored procedures Stored Procedures within the databasedataset. You will also learn how to save the results of a query Query to a Tag or a .NET variable and test the stored procedure's execution.

On this page:

Table of Contents
maxLevel3

Creating a Stored Procedure

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

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

The sections below will go over these following sections address each of the above steps in more detail.  

Step 1: Create A Database

 With SQL Server Management Studio (SSMS).

First, you need to create a Database in the used managament system, for the purpose of this example will be using SQL Server Managemenet studiousing 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 pane panel, right-click Databasesand choose New Query.

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

    Code Block
    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'.

    Code Block
    USE master;
CREATE DATABASE test; GO


  4. Click the Execute button ( or press pressing F5 ) to run the query. The new 'test' database will be Query. As a result, a new test database is created.

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

    Code Block
    themeConfluence
    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 pressing ing F5 ) to run the queryQuery. The new tables will be created.
  5. In the Object Explorer pane panel, refresh the test database by right-clicking it and selecting Refresh ( or press pressing F5). Expand If you 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 The next step is to create a third table that contains containing the primary keys from the previously created tables used to create the Store Procedure. Follow these the next steps to do so.create the new table:

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

    Code Block
    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 pressing F5 ) to run the query. The table will be created , and populated with the provided data, and the stored procedure Stored Procedure will be added.

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


Step 4: Execute The Procedure

in SQL Server Management Studio (SSMS)

At this stage, you have created a Stored Procedure and can now execute it. To execute the Procedure recently created, follow the steps belowNow 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 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.

    Code Block
    EXEC TestSP ’C019’

    This code executes , which will execute the TestSP stored procedure with the parameter value C019 .

    Code Block
    EXEC TestSP ’C019’


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

Step 5: Save The Result Of A

QueryTo A Tag Or .NET Variable 

Query

If 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 using the following code:

Code Block
@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 , as in the below example code below.:

Code Block
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 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 is added in to the Draw Environment, and its . Its Data Source was is set to the Query created above. Next, a in the previous step.

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

    Code Block
    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
    6. above
  6. , the result should be the same as in SQL Server Management Studio (SSMS).

Test Results

TestDescription

Results

Creating Procedure in SQL Server.

OK.

Running Procedure in SSMS.

OK.

Running Procedure in fs-9.1.9.

OK.


In this section...

Page Tree
root@parent
spacesV10