·
Advantages Of
Stored Sub Programs compared to Independent SQL Statements
·
Stored Procedures
·
Creating ,
Altering and Dropping
·
Optional
Parameters
·
Input and Output
Parameters
A stored procedure is a
collection of T-SQL statements. Let us suppose you are running a query number
of time office. Every time you have to write in query and execute it. Instead
of that, you can save/store the query in a stored procedure with a specific name
and you can invoke the procedure by calling that name.
Using stored procedure
user can do CREATE, UPDATE, INSERT and DELETE operations.
Syntax:
CREATE PROCEDURE/PROC <PROCEDURE_NAME>
@parameter1,2,… DATATYPE
AS
BEGIN
T-SQL statements1
T-SQL statements2
T-SQL statements3
T-SQL statements4
Etc…
END
Example:
CREATE PROCEDURE [dbo].[sp_Empinfo]
AS
BEGIN
SELECT * FROM tbl_Employee
END
ALTER:
Alter PROCEDURE PROCEDURE/PROC <PROCEDURE_NAME>
@parameter1,2,… DATATYPE
AS
BEGIN
T-SQL statements1
T-SQL statements2
T-SQL statements3
T-SQL statements4
Etc…
END
Drop
DROP PROCEDURE sp_Stateinfo
Parameters/Input
paramters:
Parameters are used to get specific data from a procedure.
In general we will pass values to where condition in the query.
Example:
CREATE PROCEDURE [dbo].[sp_Empinfo]
@empno Int
AS
BEGIN
SELECT * FROM tbl_Employee
WHERE Empno = @empno
END
Output parameters
Output parameters are used to catch the procedure result
into a variable. Output parameters are declared with out keyword.
CREATE PROCEDURE [dbo].[sp_Empinfo]
@empno Int,
@count INT Out/Output
AS
BEGIN
SELECT
@count = count(*) FROM
tbl_Employee WHERE Empno = @empno
END
To catch the output, we need to create a variable with the
same data type, which we declared in the procedure as output parameter.
DECLARE
@cnt int
EXEC
sp_Empinfo @cnt OUT
select
@cnt
Then @cnt will catch
the result from the sp_Empinfo Procedure.
Optional
parameters:
Optional parameters will contain default values. In case
user not supply any data to the
variables in the procedure, using the
default values, output will be generated.
Example: I want to write a procedure to return employees
from employee table. If you pass EmpID related employee information should
return. If you not pass any parameter, all employee information should return.
CREATE PROCEDURE ved_Opionalprarameters
@empno VARCHAR(50) = null
AS
BEGIN
SELECT * FROM tbl_Employee WHERE EmpNo = @empno OR @empno IS NULL
END
In the above example I declared @empno as output parameter.