» Documentation » SQL Reference

Create Procedure

Syntax

CREATE PROCEDURE [database_name.] procedure_name
            [ { @param data_type }   [ = default_value ]  ] [ ,...n ]
AS
 [ DECLARE  [ @param data_type }   [ = default_value ]  [ ,...n ]]
{[ BEGIN ] statements [ END ]} 

Arguments

Parameter

Description

database_name

The name of the database to which the procedure belongs.

procedure_name

The name of the new stored procedure.

@param

Is the parameter in the procedure. Multiple parameters can be declared in the procedure. The value of each parameter must be specified by user when execute procedure, unless parameter has set default value in CREATE |-

PROCEDURE statement.

The procedure's parameters and default values are stored in system.syscolumns table, where [table_id] is the procedure's id stored in the system.sysprocedures table.

Any data type parameter can be INPUT direction, although, OUTPUT or INPUT_OUTPUT parameters can't be TEXT, NTEXT or BLOB data types. The direction of the parameter is specified by the .NET providers ScimoreParameter class attribute or C++ CCommand::AddParameter function's parameter.

data_type

Is data type of the parameter. Parameter can declare any ScimoreDB data type, except uniqueidentifier.

default_value

Is a default value for the parameter. If a default value is defined, the procedure can be executed without specifying a value for that parameter. The default value can be the value of any data type, function or NULL.

Remarks

When you create procedure, the code is compiled, optimized and stored in system tables. Later, the execution plan will be used to execute procedure. The query plan will not change, unless rebuild is called.

Procedures cannot use DDL statements, like Create Table, Create Database.

Create Procedure can be executed within existing transaction scope. For instance:

BEGIN TRAN
CREATE PROCEDURE
COMMIT 

Until transaction not committed, the procedure are visible only to current transaction. If the transaction is aborted, the new procedure creation will be undone.

Example

Simple procedure with 1 parameter and default value

Use testdb;

create procedure test @p1 int = 100
as
declare @v int
begin
    set @v = @p1
    while @v > 0
    begin
        set @v = @v-1
    end

    select @p1
end;


EXECUTE test
->100
EXECUTE test @p1=1000
->1000
EXECUTE test 2000
->2000