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
