Basic SQL Server Procedure

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

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

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

Output table structure:

id (int). Identifier.
name (varchar). Fruit name.

Create a type:

CREATE TYPE [fruits_get_return_type] AS TABLE(
[id] [int] NULL,
[name] [nvarchar](max) NULL
)

Create a fruits table:

CREATE TABLE [fruits_struct](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [name] [nvarchar](max) NULL
   ) ON [PRIMARY]
GO

The procedure that receives the "id" input scalar parameter of the int type extracts a row from the fruits table:

CREATE PROCEDURE [fruits_get] @id INT
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [fruits_struct] WHERE id = @id;
END
GO