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 ---- Parameter
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.
No comments:
Post a Comment