A stored procedure is a named collection of SQL statements or control-of-flow language. We can create stored procedures for commonly used functions, and to improve performance.
Stored procedure is a named TSQL block that accepts some input, performs some tasks and may or may not returns a value.
Stored procedures are created to perform one or more DML operations over database.
Sybase Stored Procedure Syntax:
create procedure procedure_name as SQL_statements
Sybase Stored Procedure Examples:
1. Create procedure to add two numbers
create procedure empnames as select ENAME from EMP
To execute Stored procedure:
To execute a stored procedure, use the keyword execute and the name of the stored procedure, or just use the procedure’s name.
empnames execute empnames exec empnames r2schools..empnames
1> exec empnames 2> go
ENAME
——————–
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
TURNER
ADAMS
JAMES
FORD
MILLER
(13 rows affected)
(return status = 0)
1> use master 2> go 1> r2schools..empnames 2> go
ENAME
——————–
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
TURNER
ADAMS
JAMES
FORD
MILLER
(13 rows affected)