CREATE PROCEDURE

SQL COMMAND

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

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<http://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 called REBUILD DATABASE mydb PROCEDURES <href = link to rebuild > statement.

The procedure cannot use DDL statements, like CREATE TABLE, DATABSE,…

The 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

Related