In this article we will learn one of must important topic Stored Procedure in SQL Server with different examples.
Contents
What is a stored procedure in SQL?
- A Stored Procedure (SP) is a collection or a group of precompiled T-SQL statements is stored together in the database.
- It will reduce the network traffic because of the recompilation.
- Stored procedures are created to perform one or more DML operations on a database. It is nothing but a set of SQL statements that accepts some input as parameters and performs some function and may or may not receive value.
Why do you use a Stored Procedure ?
- If we use the stored procedure then we will get below advantage.
- It will reduce the network traffic.
- It will also reduce the execution time due to precompiled.
- If will not allow for SQL Injection.
- Code will be stored in the server only so indirectly We will achieve the security.
Important Note
When naming user defined Stored Procedures. Microsoft recommends not use SP_ as a prefix. All system Stored Procedures prefixed with SP_. This avoids any ambiguity between user defined and system define Stored Procedures (SP).
Types of Stored Procedure in SQL
- System Defined Stored Procedure
- User-Defined Stored Procedure
System Defined Stored Procedure
These stored procedures are already defined in SQL Server. These are physically stored in hidden SQL Server resource databases and logically appear in the sys schema of each user-defined and system-defined database. This process begins with sp_ prefix. So, we don’t use this prefix when naming user-defined processes.
User-Defined Stored Procedure
In SQL Server, user-defined stored procedures are created in a user-defined database by database developers or database administrators. These stored processes include another SQL statement for selecting, updating, inserting, or deleting records from database tables.
Now we will understand the syntax of Stored Procedure such as create/alter/delete from our database.
Create Syntax
CREATE PROCEDURE Stored_Procedure_Name
AS
BEGIN
———-
—— Your Code
———-
END
As per the above syntax, we will use create procedure followed by stored procedure name. Then write your code in begin and end block. Let’s see the below example.
CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
SELECT * FROM EMPLOYEE
END
As per the above example, We have created one Stored Procedure(SP), name as GetEmployeeDetails. in this SP, we are fetching data from employee table so we wrote the select query inside in Begin and End block.
Modify Syntax
ALTER PROCEDURE Stored_Procedure_Name
AS
BEGIN
———-
—— Your Code
———-
END
The above alter command will use to modify the SP in sql server.
ALTER PROCEDURE GetEmployeeDetails
AS
BEGIN
SELECT * FROM EMPLOYEE e JOIN DEPARTMENT d on e.DEPTID = d.ID
END
On our above script we have modify the SP without changes it’s name. Alter will use to modify the sp in SQL Server.
Execute Stored Procedure in SQL Server
EXEC Stored_Procedure_Name
EXECUTE Stored_Procedure_Name
SQL Server provides two way to execute the stored procedure in SQL. we can use run by execute as well as exec (short form execute) commands.
Let’s run our “GetEmployeeDetails” stored procedure in SQL Server.
EXEC GetEmployeeDetails
The above piece of code will run GetEmployeeDetails sp in SQL Server and it will return the result. We can also run the sp by using below statement.
EXECUTE GetEmployeeDetails
Delete Stored Procedure in SQL Server
DROP PROCEDURE Stored_Procedure_Name
or we can write
DROP PROC Stored_Procedure_Name
We can write can one of statement to remove procedure in SQL Server.
Example:-
DROP PROC GetEmployeeDetails
Stored Procedure in SQL with Parameters
- Input parameters allow the caller to send a data value into the stored procedure or function.
- Output parameters allow the stored procedure to pass a data value back to the caller.
- Every stored procedure returns an integer return code to the caller. If the stored procedure does not explicitly set a value for the return code, then the return code is 0.
Important Note
If you don’t specify the output keyword, when executing the stored procedure and initial value will be null for output variable.
Syntax
CREATE/ALTER PROCEDURE Stored_Procedure_Name
@PARAMETER1 DATATYPE,
@PARAMETER2 DATATYPE,
……………
@OUTPARAMETER1 DATATYPE OUTPUT,
@OUTPARAMETER2 DATATYPE OUTPUT
…………
AS
BEGIN
———-
—— Your Code
———-
SET @OUTPARAMETER1 = VALUE1
SET @OUTPARAMETER2 = VALUE2
END
Output keyword used for output parameters.
Now, We see one example to understand more details about stored procedure with parameters.
CREATE PROCEDURE GETEMPLOYEECOUNT
@DEPARTMENT_NAME VARCHAR(50),
@TOTAL_EMPLOYEE_BY_DEPARTMENT INT OUTPUT
AS
BEGIN
SELECT @TOTAL_EMPLOYEE_BY_DEPARTMENT = COUNT(*) FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPTID = D.ID WHERE D.NAME = @DEPARTMENT_NAME
END
As per the above example, we are passing two parameter into our stored procedure “GetEmployeeCount”. Here, @Department_Name parameter is used for pass the value from outside. so it is called as input parameter, on other side, @Tota_Employee_by_Department parameter is used to get the value from stored procedure. so it is called output parameters.
It will return the value number of employee based input parameter @Department_Name. Let’s see the result by execute the above SP in SQL Server.
Execute SP with Parameters
While we execute Stored Procedure with parameter,
Execute SP with Parameters
DECLARE @INPUTPARAMETER1 DATATYPE = INPUTVALUE
DECLARE @OUTPARAMETER1 DATATYPE
EXECUTE Stored_Procedure_Name INPUTPARAMETER1, @OUTPARAMETER1 OUT
PRINT @OUTPARAMETER1
Example-
DECLARE @DEPARTNAME VARCHAR(20) = 'IT'
DECLARE @TOTALEMP INT
EXEC GETEMPLOYEECOUNT @DEPARTNAME, @TOTALEMP OUT
PRINT @TOTALEMP
We can also write the below script to execute it on SQL Server.
DECLARE @return_value int,
@TOTAL_EMPLOYEE_BY_DEPARTMENT int
EXEC @return_value = [dbo].[GETEMPLOYEECOUNT]
@DEPARTMENT_NAME = N'IT',
@TOTAL_EMPLOYEE_BY_DEPARTMENT = @TOTAL_EMPLOYEE_BY_DEPARTMENT OUTPUT
SELECT @TOTAL_EMPLOYEE_BY_DEPARTMENT as N'@TOTAL_EMPLOYEE_BY_DEPARTMENT'
SELECT 'Return Value' = @return_value
Return data using result sets
If you include a SELECT statement in the body of a stored procedure, the rows specified by the SELECT statement will be sent directly to the client. For large result sets, the stored procedure execution won’t continue to the next statement until the result set has been completely sent to the client.
Syntax
CREATE/ALTER PROCEDURE Stored_Procedure_Name
AS
BEGIN
–DQL STATEMENT
RETURN
END
Example:-
ALTER PROCEDURE GetEmployeeDetails
AS
BEGIN
SELECT * FROM EMPLOYEE e JOIN DEPARTMENT d on e.DEPTID = d.ID
RETURN
print 'Welcome to InfoSyntax'
END
View Details Of Stored Procedure in SQL
- View the information about stored procedure like parameters name, their datatypes etc.
- It can be used with any database object like tables, SP, trigger and on so
- You can also press ALT+F1, when the name of object is highlighted
Syntax
SP_HELP Stored_Procedure_Name
Exmple:-
SP_HELP GetEmployeeDetails
Similarly We can use this stored producer against table to see the details. Please follow the below commands to view the details about SQL table.
SP_HELP Employee
View Dependencies Of Stored Procedure in SQL
- The below syntax is used to view the dependencies of the stored procedure.
- It is a system stored procedure.
- It can be able to view the dependencies of other objects like Tables, Views, etc.
Syntax
SP_DEPENDS Stored_Procedure_Name
Example:-
SP_DEPENDS GetEmployeeDetails
As per the above statement, we can view the dependencies of GetEmployeeDetails in our SQL Server.
Similarly we can see the dependency of table in SQL Server.
SP_DEPENDS employee
We have used employee table in our stored Procedure “GetEmployeeDetails”, So return dependent SP name.
View The Text Of Stored Procedure in SQL
The below query is used to see the text of the stored procedure in the SQL server.
Syntax
SP_HELPTEXT Stored_Procedure_Name
Example:-
SP_HELPTEXT GetEmployeeDetails
We can see the below result from our database.
SQL Join with interview Question and answers
Encrypt The Text Of The Stored Procedure in SQL
- WITH ENCRYPTION is used to encrypting the text of stored procedure in SQL server.
- It is not possible to view the text of encrypted stored procedure in SQL server.
Syntax
CREATE/ALTER PROCEDURE Stored_Procedure_Name
WITH ENCRYPTION
AS
BEGIN
———-
—— Your Code
———-
END
Example:-
ALTER PROCEDURE GetEmployeeDetails
WITH ENCRYPTION
AS
BEGIN
SELECT * FROM EMPLOYEE e JOIN DEPARTMENT d on e.DEPARTMENTID = d.DEPARTMENTID
END
Now we have encrypted the text of our stored procedure in SQL Server. We will see text in SP by using SP_HELPTEXT system SP.
SP_HELPTEXT GetEmployeeDetails
Unable to get the text from our sp GetEmployeeDetails because it is encrypted by using WITH ENCRYPTION keyword.
Advantages Of Using Stored Procedure in SQL
Better Performance :-
The procedure calls are quick and efficient as stored procedures are compiled once and stored in executable form. Hence the response is quick. The executable code is automatically cached, hence lowers the memory requirements.
Higher Productivity :-
Since the same piece of code is used again and again so, it results in higher productivity.
Ease of Use :-
To create a stored procedure, one can use any Java Integrated Development Environment (IDE). Then, they can be deployed on any tier of network architecture.
Scalability:-
Stored procedures increase scalability by isolating application processing on the server.
Maintainability:-
Maintaining a procedure on a server is much easier than maintaining copies on various client machines, this is because scripts are in one location.
Security:-
Avoid SQL injection attacks
Disadvantages Of Using Stored Procedure in SQL
Testing:-
Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime.
Debugging:-
Depending on the database technology, debugging stored procedures will either be very difficult or not possible at all. Some relational databases such as SQL Server have some debugging capabilities.
Versioning:-
Version control is not supported by the stored procedure.
Cost:-
An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost.
Portability:-
Complex stored procedures will not always port to upgraded versions of the same database. This is especially true in case of moving from one database type(Oracle) to another database type(MS SQL Server).
Please refer the link more details about Stored Procedure from Microsoft.
Please watch our SQL Server Tutorial video from Infosyntax Youtube Channel. Share you valuable comments and share the video or post.
Thank You so much read this article……..
