Sybase : About Stored Procedures !

Stored Procedures

A Stored Procedure is a named collection of SQL Statements or control flow of language.

You can create stored procedures for commonly used functions and to increase performance.

Stored Procedures can:

• Take parameters
• Call other procedures
• Return a status value to calling procedure or batch to indicate success or failure and the reason for failure.
• Return values of parameters to a calling procedure or batch.
• Be executed on remote Adaptive Servers

How to create stored Procedures ?

Create procedure <proc-name>

as

query./ SQL Statements

example :

create procedure namelist

as select name from sysusers

Execution of Procedure

Different ways to execute stored procedure :

execute server_name.[database_name].[owner].procedure_name

execute <proc-name>

exec <proc-name>

<proc-name>

you can assign parameters to a stored procedure while creating / executing Stored Procedure

Parameters are nothing but an arguement to a stored procedure.

you can optionally declare one or more parameters in a create procedure statement.

The values present in the procedure have to be provided at the time of execution or can be hard coded at the time of creating the stored procedure.

parameters should be preceded with an @ symbol at the time of creating a stored procedure.

Parameter names, including the @ sign ,can be a maximum of upto 255 bytes long.

Create procedure <proc-name>

@<parameter> datatype

as

sql statement/ queries.

While Execution

exec proc-name <parameter value>

A stored procedure can do the following :

• create a temporary table.
• insert, update or delete data
• Run queries on temporary table
• call other procedures that refer to the temporary table

While creating temp tables, the table name should be preceded by # symbol.

create table #<table-name>

(

variables datatypes constraints

)

Execution of stored procedures after a timely delay

The waitfor command delays execution of a stored procedure at a specified time

or until a specified amount of time has passed.

For example, to execute the procedure testproc in half an hour:

begin

waitfor delay "0:30:00"

exec testproc

end

 ref : Sybase manual