SDAC

Stored Procedures

This tutorial describes how to insert data into tables using the components. This tutorial describes how to work with stored procedures using the TMSStoredProc component.

  1. Requirements
  2. General information
  3. Input parameters
  4. Output parameters
  5. Input/output parameters
  6. Return values
  7. Returning result sets

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQL Server" and "Connecting To SQL Server Compact"), how to create necessary objects on the server (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

General information

A stored procedure is a schema object that consists of a set of SQL statements, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Large or complex processing that might require execution of several SQL statements is moved into stored procedures, and all applications call the procedures only.

Objects similar to stored procedures are stored functions. Almost everything that is true for procedures, holds for functions as well. The main difference between these objects is that function has a return value, and procedure has not. Stored procedures and functions may have input, output, and input/output parameters.

Input parameters

Input parameter is a parameter which value is passed into a stored procedure/function module. The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

For example, the following procedure inserts a new row into the table dept:

CREATE PROCEDURE InsertDept
  @deptno INT,
  @dname VARCHAR(14),
  @loc VARCHAR(13)
AS
BEGIN
  INSERT INTO dept(deptno, dname, loc) VALUES(@deptno, @dname, @loc);
END

It needs to receive the values to be inserted into the new record, and thus the procedure has three input parameters, corresponding to each field of the table. This procedure may be executed as follows:

EXECUTE InsertDept 10,'ACCOUNTING','NEW YORK'

To execute the InsertDept stored procedure using the TMSStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMSStoredProc;
begin
  sp := TMSStoredProc.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    sp.Connection := con;

    // choose a stored procedure name to execute
    sp.StoredProcName := 'InsertDept';

    // build a query for a chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // assign parameter values
    sp.ParamByName('deptno').AsInteger := 10;
    sp.ParamByName('dname').AsString := 'ACCOUNTING';
    sp.ParamByName('loc').AsString := 'NEW YORK';

    // execute the stored procedure
    sp.Execute;
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMSStoredProc* sp = new TMSStoredProc(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    sp->Connection = con;

    // choose a stored procedure name to execute
    sp->StoredProcName = "InsertDept";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp->PrepareSQL();

    // assign parameter values
    sp->ParamByName("deptno")->AsInteger = 10;
    sp->ParamByName("dname")->AsString = "ACCOUNTING";
    sp->ParamByName("loc")->AsString = "NEW YORK";

    // execute the stored procedure
    sp->Execute();
  }
  __finally
  {
    sp->Free();
  }
}

Output parameters

Output parameter is a parameter which value is passed out of the stored procedure/function module. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable.

For example, the following stored procedure returns the count of records in the table dept:

CREATE PROCEDURE CountDept
  @cnt INT OUT
AS
BEGIN
  SELECT @cnt = count(*) FROM dept;
END

Note 1: SQL Server treats output parameters as input/output parameters.

Note 2: SQL Server stored functions do not support output parameters.

To execute the CountDept stored procedure using the TMSStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMSStoredProc;
begin
  sp := TMSStoredProc.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    sp.Connection := con;

    // choose a stored procedure name to execute
    sp.StoredProcName := 'CountDept';

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // execute the stored procedure
    sp.Execute;

    // show the value of the output parameter
    ShowMessage(IntToStr(sp.ParamByName('cnt').AsInteger));
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMSStoredProc* sp = new TMSStoredProc(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    sp->Connection = con;

    // choose a stored procedure name to execute
    sp->StoredProcName = "CountDept";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp->PrepareSQL();

    // execute the stored procedure
    sp->Execute();

    // show the value of the output parameter
    ShowMessage(IntToStr(sp->ParamByName("cnt")->AsInteger));
  }
  __finally
  {
    sp->Free();
  }
}

Input/output parameters

An input/output parameter is a parameter that functions as an IN or an OUT parameter or both. The value of the IN/OUT parameter is passed into the stored procedure/function and a new value can be assigned to the parameter and passed out of the module. An IN/OUT parameter must be a variable, not a constant. It can be found on both sides of an assignment. In other words, an IN/OUT parameter behaves like an initialized variable.

For example, the following stored procedure returns the salary with five percents bonus:

CREATE PROCEDURE GiveBonus
  @sal FLOAT OUT
AS
BEGIN
  SET @sal = @sal * 1.05;
END

Note 1: SQL Server does not have input/output parameters as such. SQL Server treats output parameters as input/output parameters.

Note 2: SQL Server stored functions do not support input/output parameters.

To execute the GiveBonus stored procedure using the TMSStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMSStoredProc;
begin
  sp := TMSStoredProc.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    sp.Connection := con;

    // choose a stored procedure name to execute
    sp.StoredProcName := 'GiveBonus';

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assign it to the SQL property
    sp.PrepareSQL;

    // assign parameter values
    sp.ParamByName('sal').AsFloat := 500.5;

    // execute the stored procedure
    sp.Execute;

    // show the value of the input/output parameter
    ShowMessage(FloatToStr(sp.ParamByName('sal').AsFloat));
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMSStoredProc* sp = new TMSStoredProc(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    sp->Connection = con;

    // choose a stored procedure name to execute
    sp->StoredProcName = "GiveBonus";

    // build a query for chosen stored procedure based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp->PrepareSQL();

    // assign parameter values
    sp->ParamByName("sal")->AsFloat = 500.5;

    // execute of the stored procedure
    sp->Execute();

    // show the value of the input/output parameter
    ShowMessage(FloatToStr(sp->ParamByName("sal")->AsFloat));
  }
  __finally
  {
    sp->Free();
  }
}

Return values

In SQL Server, both stored procedures and stored functions can return values that indicate the result of the execution. For example, the GiveBonus stored procedure (that is described above) returns a value of 0. Usually, a zero value indicates success and a nonzero value indicates failure. The following modified version of the GiveBonus stored procedure returns a value of 1 if the value of the @sal parameter is null or negative, and 0 otherwise:

CREATE PROCEDURE GiveBonus
  @sal FLOAT OUT
AS
BEGIN
  IF (@sal IS NULL) OR (@sal < 0)
    RETURN 1;
  SET @sal = @sal * 1.05;
  RETURN 0;
END

Note 1: SQL Server stored procedures can return only integer values. When a stored procedure returns other values, for example, a string value, SQL Server converts it to the integer value.

Note 2: Stored functions can return values of any type.

To execute the GiveBonus stored procedure using the TMSStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMSStoredProc;
begin
  sp := TMSStoredProc.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    sp.Connection := con;

    // choosing a stored procedure name to execute
    sp.StoredProcName := 'GiveBonus';

    // building a query for chosen stored procedure based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp.PrepareSQL;

    // assigning parameter values
    sp.ParamByName('sal').AsFloat := 500.5;

    // executing of the stored procedure
    sp.Execute;

    // showing the return value
    ShowMessage(IntToStr(sp.ParamByName('return_value').AsInteger));

    // showing the value of the input/output parameter
    ShowMessage(FloatToStr(sp.ParamByName('sal').AsFloat));
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMSStoredProc* sp = new TMSStoredProc(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    sp->Connection = con;

    // choosing a stored procedure name to execute
    sp->StoredProcName = "GiveBonus";

    // building a query for chosen stored procedure based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp->PrepareSQL();

    // assigning parameter values
    sp->ParamByName("sal")->AsFloat = 500.5;

    // executing of the stored procedure
    sp->Execute();

    // showing the return value
    ShowMessage(IntToStr(sp->ParamByName("return_value")->AsInteger));

    // showing the value of the input/output parameter
    ShowMessage(FloatToStr(sp->ParamByName("sal")->AsFloat));
  }
  __finally
  {
    sp->Free();
  }
}

The same task can be resolved by using stored functions as well. For example, the following stored functions returns a value of 0 if the value of the @sal parameter is null or negative, and the correct bonus otherwise:

CREATE FUNCTION GiveBonus(
  @sal FLOAT
)
RETURNS FLOAT
AS
BEGIN
  IF (@sal IS NULL) OR (@sal < 0)
    RETURN 0;
  RETURN @sal * 1.05;  
END

As is was mentioned previously, SQL Server stored functions do not support output and input/output parameters. That is why the behaviour of the GiveBonus stored function is slightly different from the behaviour of the GiveBonus stored procedure.

To execute the GiveBonus stored function using the TMSStoredProc component, the following code can be used:

[Delphi]

var
  sp: TMSStoredProc;
begin
  sp := TMSStoredProc.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    sp.Connection := con;

    // choosing a stored function name to execute
    sp.StoredProcName := 'GiveBonus';

    // building a query for chosen stored function based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp.PrepareSQL;

    // assigning parameter values
    sp.ParamByName('sal').AsFloat := 500.5;

    // executing of the stored function
    sp.Execute;

    // showing the return value
    ShowMessage(FloatToStr(sp.ParamByName('return_value').AsFloat));
  finally
    sp.Free;
  end;
end;

[C++Builder]

{
  TMSStoredProc* sp = new TMSStoredProc(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    sp->Connection = con;

    // choosing a stored function name to execute
    sp->StoredProcName = "GiveBonus";

    // building a query for chosen stored function based on the Params and StoredProcName properties, and assigning it to the SQL property
    sp->PrepareSQL();

    // assigning parameter values
    sp->ParamByName("sal")->AsFloat = 500.5;

    // executing of the stored function
    sp->Execute();

    // showing the return value
    ShowMessage(FloatToStr(sp->ParamByName("return_value")->AsFloat));
  }
  __finally
  {
    sp->Free();
  }
}

Returning result sets

Besides scalar variables, a stored procedure can return result sets, i.e. the results of a SELECT statement. This question is discussed in details in the tutorial "Working With Result Sets Using Stored Procedures".

© 1997-2019 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback