Versions Compared

Old Version 6

changes.mady.by.user admin

Saved on

compared with

New Version 7

changes.mady.by.user Support

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. 

This section will guide you through the process of creating, configuring, and executing stored procedures within the database. You will also learn how to save the results of a 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

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

1.

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.
  1. In the Object Explorer pane, 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

  1. code

:

  1. below.

    Code Block
    USE master;
CREATE DATABASE test; GO
This code

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

4.

  1. Click the

"

  1. Execute

"

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

5.

  1. In the

"

  1. Object Explorer

"

  1. pane, click the "Refresh" button next to

the "

  1. Databases

" folder

  1. (or press F5). You should see the new 'test'

database listed under the "Databases" folder

  1. Database listed.

Creating

Step 2: Create Tables with Primary Keys

After creating the test Database, you need to create a Table with the primary keys (Spec

id

Id and Char

id)1. 

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 was created
  1. Database.
2.
  1. In the Object Explorer pane, expand the Databases folder
and locate the 'test' database. Right
  1. , right-click on the '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

  1. code

:

  1. below.

    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
This code

  1. It creates two tables,

'

  1. dbo.Characteristic

'

  1. and

'

  1. dbo.Specification

'

  1. , with

primary

  1. theprimary keys

'

  1. Char_Id

'

  1.   and

'

  1. Spec_ID

'

  1. , respectively. It also inserts sample data into both tables.

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

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

1. Open

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.
  1. In the Object Explorer
pane
  1.  pane, 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

  1. code

:

  1. below.

    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

  1. It creates a new

table '

  1. Table dbo.Active_Spec

'

  1.   containing the

primary keys

  1. Primary Keys from the other two

tables '

  1. Tables dbo.Characteristic

' and '

  1.  and dbo.Specification

'

  1. .It also inserts sample data into the

'

  1. dbo.Active_Spec

'

  1. table and creates a stored procedure named

'

  1. dbo.TestSP

'

  1. .

4.

  1. Click the Execute

 button

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

  1. In the Object Explorer pane,

refresh

  1. right-click the

'test' database by right-clicking it and selecting

  1. test Database and select Refresh (or press F5). 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. , and the

'

  1. dbo.TestSP

'

  1.   stored procedure under the Programmability → Stored Procedures folder.

Executing


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

1. Open

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
and 'TestSP' stored procedure were
  1. was created.
2.
  1. In the Object Explorer
pane
  1.  pane, 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

  1. code

:

  1. below.

    Code Block
    EXEC TestSP ’C019’

    This code executes the

'

  1. TestSP

'

  1. stored procedure with the parameter value

'

  1. C019

'

  1. .

4.

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

Step 5: Save the Result of a Query to a Tag or .NET Variable 

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 with 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 the 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

1. Go to the

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

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 Data Grid was added in the Draw Environment

, a DataGrid was added

  1. , and its

DataSource

  1. Data Source was set to the Query created above. Next, a button is used to run the script 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.
  1. After executing the code above, 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