Stored Procedure - DotNet and DataBase

Sunday, 29 July 2018

Stored Procedure



A stored procedure is a collection of T-SQL statements. Let us suppose you are running a query number of times. Every time you have to write in the 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 parameters:
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 without the 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 an 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 of the user does not supply any data to the variables in the procedure, using the default values, the 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 do 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 an output parameter.




No comments:

Post a Comment

x

Get Updates On

Discussion updates

Straight Into Your INBOX!

Enter your email address to subscribe to this website and receive notifications of new posts by email.