Basic SQL Server Procedure

To develop a basic procedure, there is a table with basic SQL Server data types and corresponding internal platform types:

Detailed description of SQL Server is given in description of Data Types, Data Type Synonyms.

Basic Table Type

Types, tables and procedures are created in the standard database language. A basic table type is declared using the syntax:

CREATE TYPE <type name> AS TABLE (
    <column name> <column type>
);

Basic Stored Procedure

SQL Server has stored procedures and functions that have important differences:

• Stored procedures have restriction for returned value. They do not support the RETURN word, but procedures can return the cursor with one or several consequent tables. Procedure definition syntax does not enables the user to set input table structure.

• Functions cannot change data on disk even if procedure is called inside the function.

Functions cannot be used to implement basic stored procedures because data change scenarios will no be implemented due to function restrictions. It is required to implement basic stored procedures based on procedures. If there is the some_proc procedure, a basic table type named some_proc_return_type is must be determined to determkine output table structure.

The linked table type for procedure is a basic table type, which name differs from name of the linked procedure with the "_return_type" suffix. This type provides type of procedure output table.

Basic stored procedure in MS SQL is a combination of stored procedure and linked table type. Procedure requirements are identical to requirements to basic stored procedure, except for the item about returned structure.

Example

Consider the example of adding a new record to the fact table that contains the attributes:

• id (int). Record identifier.

• name (varchar). Fruit name.

A new record is added to the table using stored procedures that should be created in the database. To execute stored procedures, use the rpc API method. As input parameters for procedures, set table attributes that are passed in tabular or scalar format.

Examples of executing procedures depending on input parameter passing type:

CREATE PROCEDURE [fruits_add_table_struct]
@upsert_rows fruits_struct READONLY
AS
    BEGIN
 
   SET NOCOUNT ON;
   SET IDENTITY_INSERT [fruits_struct] ON;
 
   INSERT INTO  [fruits_struct](id, name)
   SELECT id, name FROM @upsert_rows
   WHERE id NOT IN (
      SELECT id FROM [fruits_struct]
   ) AND id IS NOT NULL;
 
   SET IDENTITY_INSERT [fruits_struct] OFF;
 
   INSERT INTO [fruits_struct](name)
   SELECT name FROM @upsert_rows WHERE id IS NULL;
 
   DECLARE @id NVARCHAR(max);
   DECLARE [cursor] CURSOR LOCAL READ_ONLY
   FOR    (
      SELECT id FROM @upsert_rows
   );
 
   OPEN [cursor];
   FETCH NEXT FROM [cursor] INTO @id;
 
   WHILE @@FETCH_STATUS = 0
   BEGIN
      UPDATE [fruits_struct]
      SET NAME = (
         SELECT NAME    FROM @upsert_rows WHERE id = @id
      )
      WHERE id = @id;
      FETCH NEXT FROM [cursor] INTO @id;
   END;
 
   CLOSE [cursor];
   DEALLOCATE [cursor];
   end
 
    SELECT * FROM fruits_struct WHERE id = (SELECT id from @upsert_rows)
go

create type fruits_add_table_struct_return_type as table
(
    id   int,
    name nvarchar(max)
)
go

CREATE PROCEDURE [fruits_add_enum]
@id INT,
@name NVARCHAR (max)
 
as begin
    Set nocount on;
    SET IDENTITY_INSERT [fruits_struct] ON;
    IF NOT EXISTS(select * from fruits_struct where id=@id)
    INSERT INTO fruits_struct (id,name) VALUES (@id,@name);
    ELSE IF EXISTS(select * from fruits_struct where id=@id)
    UPDATE [fruits_struct] SET name = @name
    WHERE id=@id;
 
   SELECT * from fruits_struct WHERE id=@id
 
end
go

create type fruits_add_enum_return_type as table
(
    id   int,
    name nvarchar(max)
)
go

After executing one of the procedures a new record is added to the fruits table, if value of the specified record identifier (the id attribute) differs from existing values in the table. If value of the specified record identifier matches with the existing value in the table, the fruit name (the name attribute) will be changed for the name passed in the request.

See also:

Setting Up Integration with Table Data Sources | Basic SOAP/XML Procedure