Versions Compared

Old Version 5

changes.mady.by.user admin

Saved on

compared with

New Version Current

changes.mady.by.user admin

Saved on

Key

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

Overview

Stored procedures play a crucial role in managing complex data operations and enhancing database performance. A stored procedure 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 . Utilizing stored procedures can help streamline your data processing tasks, reduce network traffic, and improve overall efficiency.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

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

Creating a DatabaseOpen

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

1.

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.
  1. In the Object Explorer
pane
  1. panel, right-click
on the
  1. Databases
folder
  1. and choose New Query
 from the context menu
  1. .
3.

  1. In the

new query

  1. New Query window, paste the

following code:
Code Block
USE master;
CREATE DATABASE test; GO
This code tells

  1. code below.  It tells the SQL Server to switch to the

'

  1. master

'

  1. database and then create a new database called

'

  1. test

'.

  1. .

    Code Block
    USE master;
CREATE DATABASE test; GO


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

  3. In the Object Explorer panel, click the Refresh button next to Databases or pressing F5

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)

  1. . You should see the new

'

  1. test

' database listed under the "Databases" folder.

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

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

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

  1. In the

new query

  1. New Query window, paste the

following code:

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


  2. Click the Execute
 button (
  1. button or
press
  1. pressing ing F5
)
  1. to run the
query
  1. Query. The new tables will be created
and populated with the provided data
  1. .
5.
  1. In
the
  1. the Object Explorer
pane
  1. panel, refresh the
'
  1. test
'
  1. database by right-clicking it and selecting Refresh
(
  1. or
press
  1. pressing F5
)
  1. .
Expand
  1. If you expand the
'
  1. test
'
  1. database,
and
  1. you should see the newly created
'
  1. dbo.Characteristic
'
  1. and
'
  1. dbo.Specification
'
  1.   tables under
the
  1. Tables
folder
  1. .
Creating

Step 3: Create a

Third

Table

That Contains

containing the

PrimaryKeys

primary keys from the

Other Two Tables1. Open

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
(SSMS)
  1. and connect to the SQL Server instance where the
'
  1. test
'
  1. database was created.
2.
  1. In the Object Explorer
pane
  1. panel, expand the Databases folder
and locate the 'test' database. Right
  1. , right-click on the
'
  1. test
' database
  1. Database, and choose New Query
 from the context menu
  1. .
3.

  1. In the

new query

  1. New Query window, paste the

following code:

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

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.


  2. Click the Execute

 button (

  1. button or

press

  1. pressing F5

)

  1. to run the query. The table will be created

,

  1. and populated with the provided data, and the

stored procedure

  1. Stored Procedure will be added.

5.

  1. In

the

  1. the Object Explorer

pane

  1.  panel,

refresh

  1. right-click the

'test' database by right-clicking it and selecting Refresh (

  1. test Database, select Refresh, or press F5

)

  1. . . Expand the

'

  1. test

' database

  1. Database, and you should see the newly created

'

  1. dbo.Active_Spec

'

  1. table under

the

  1. Tables

folder and

  1. . You find the

'

  1. dbo.TestSP

' stored procedure

  1.   Stored Procedure under the Programmability → Stored Procedures folder.

Executing Procedure in SQL Server Management Studio (SSMS)

1. Open


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
(SSMS)
  1. and connect to the SQL Server instance where the
'
  1. test
' database and 'TestSP' stored procedure were
  1. database was created.
2.
  1. In the Object Explorer
pane
  1. panel, expand the Databases folder
and locate the 'test' database. Right
  1. , right-click on the
'
  1. test
' database
  1. Database, and choose New Query
 from the context menu
  1. .
3.

  1. In the

new query

  1. New Query window, paste the

following code:
Code Block
EXEC TestSP ’C019’
This code executes the 'TestSP'

  1. code below, which will execute the TestSP stored procedure with the parameter value

'

  1. C019

'

  1.  .

4.
  1. Code Block
    EXEC TestSP ’C019’


  2. Click the Execute button

(

  1. or press F5

)

  1. to run the

query

  1. Query. The stored procedure will be executed, and the results will be displayed in the Results

 pane

  1. panel, below the query window. The below image shows an example of the query result.

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

Step 5: Save the result of a query

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

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

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


Configuring the

Project1. Go to 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
  1. the SQL Server provider
. The user must have administrator privileges in order to input the logon name and password
  1. .

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

The new DB was named SQLDB in this exampleImage Removed

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

  1. A DataGrid is added to the Draw Environment

, a DataGrid was added, and its DataSource was

  1. . Its Data Source is set to the Query created

above. Next, a

  1. in the previous step.

  2. A button is

used

  1. configured to run a Script using the

script

  1. 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);
}
4.


  2. After clicking the button and executing the code
above
  1. , 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@parentDatasets (SQL Queries)
spacesV10