Basic SQL Server Procedure

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

Basic Table Type

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

NOTE. Names and types of basic table type attributes should match the names and types of returned procedure attributes. The order of basic table type attributes should match with the order of procedure attributes.

Basic Stored Procedure

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:

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.

Tabular Scalar

To add a new record to table:

Create a procedure named fruits_add_table_struct:

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 a basic tabular type fruits_add_table_struct_return_type:

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

Execute the created procedure using the rpc API method:

curl -v '<IP address or DNS server name>/api/v1/rpc/<resource name>/' -H "Authorization: Bearer <user token>" -H "Content-Type: application/json" -d "{"<record identifier>": <fruit name>}"

To add a new record to table:

Create a procedure named fruits_add_enum:

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 a basic tabular type fruits_add_enum_return_type:

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

Execute the created procedure using the rpc API method:

curl -v '<IP address DNS server name>/api/v1/rpc/<resource name>/' -H "Authorization: Bearer <user token>" -H "Content-Type: application/json" -d '{"@id": <record identifier>,"@name": "<fruit name>"}'

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.

SQL Server basic scalar type	Pseudonyms for SQL Server type	Internal platform type
int	integer	bigint
bigint	-	bigint
decimal[(p[, s]]	Dec[(p[, s]]	double
numeric[(p[, s]]	-	double
float[(n)]	-	double
datetimeoffset	-	text
varchar[(n \| max)]	char varying character varying (n)	text
text	-	text
nvarchar[(n \| max)]	national character varying (n) national char varying (n)	text
ntext	national text	text
varbinary[(n \| max)]	binary varying (n)	bytea