How To Find Stored Procedure In Sql Server

In this article, we volition larn how to create stored procedures in SQL Server with different examples.

SQL Server stored procedure is a batch of statements grouped every bit a logical unit of measurement and stored in the database. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure, returns the consequence set if any.

To understand differences betwixt functions and stored procedures in SQL Server, you can refer to this commodity, Functions vs stored procedures in SQL Server and to learn about Partial stored procedures in SQL Server, click Partial stored procedures in SQL Server.

Benefits of using a stored process

It can exist easily modified: We can easily modify the code inside the stored procedure without the need to restart or deploying the application. For example, If the T-SQL queries are written in the application and if we need to change the logic, nosotros must modify the lawmaking in the application and re-deploy it. SQL Server Stored procedures eliminate such challenges by storing the lawmaking in the database. and then, when we want to alter the logic inside the procedure we can just do it by simple Alter Process statement.

Reduced network traffic: When we employ stored procedures instead of writing T-SQL queries at the awarding level, only the process name is passed over the network instead of the whole T-SQL code.

Reusable: Stored procedures can be executed by multiple users or multiple customer applications without the need of writing the lawmaking again.

Security: Stored procedures reduce the threat past eliminating directly access to the tables. nosotros can as well encrypt the stored procedures while creating them and then that source code inside the stored procedure is not visible. Use tertiary-political party tools like ApexSQL Decrypt to decrypt the encrypted stored procedures.

Performance: The SQL Server stored procedure when executed for the first fourth dimension creates a plan and stores information technology in the buffer puddle and then that the program can be reused when it executes adjacent time.

I am creating sample tables that will be used in the examples in this commodity.

four

five

nine

fifteen

sixteen

CREATE Table Product

( ProductID INT , ProductName VARCHAR ( 100 ) )

Get

CREATE Tabular array ProductDescription

( ProductID INT , ProductDescription VARCHAR ( 800 ) )

INSERT INTO Product VALUES ( 680 , 'HL Route Frame - Black, 58' )

, ( 706 , 'HL Road Frame - Red, 58' )

, ( 707 , 'Sport-100 Helmet, Red' )

INSERT INTO ProductDescription VALUES ( 680 , 'Replacement mountain wheel for entry-level rider.' )

, ( 706 , 'Sturdy alloy features a quick-release hub.' )

, ( 707 , 'Aerodynamic rims for smooth riding.' )

Creating a uncomplicated stored process

We volition create a uncomplicated stored process that joins two tables and returns the issue ready as shown in the following instance.

CREATE Procedure GetProductDesc

Brainstorm

SET NOCOUNT ON

SELECT P . ProductID , P . ProductName , PD . ProductDescription FROM

Production P

INNER JOIN ProductDescription PD ON P . ProductID = PD . ProductID

END

We tin use 'EXEC ProcedureName' to execute stored procedures. When we execute the procedure GetProductDesc, the effect set looks like below.

result set of a SQL Server stored procedure

Creating a stored process with parameters

Let u.s.a. create a SQL Server stored procedure that accepts the input parameters and processes the records based on the input parameter.

Post-obit is the example of a stored procedure that accepts the parameter.

CREATE Process GetProductDesc_withparameters

( @ PID INT )

Every bit

BEGIN

Set up NOCOUNT ON

SELECT P . ProductID , P . ProductName , PD . ProductDescription FROM

Product P

INNER JOIN ProductDescription PD ON P . ProductID = PD . ProductID

WHERE P . ProductID = @ PID

END

EXEC GetProductDesc_withparameters 706

While executing the stored procedure we need to pass the input parameter. Please refer to the below prototype for the effect set.

SQL Server stored procedure with parameters

Creating a stored procedure with default parameters values

Following is the example of a stored procedure with default parameter values.

CREATE PROCEDURE GetProductDesc_withDefaultparameters

( @ PID INT = 706 )

Brainstorm

SET NOCOUNT ON

SELECT P . ProductID , P . ProductName , PD . ProductDescription FROM

Product P

INNER Join ProductDescription PD ON P . ProductID = PD . ProductID

WHERE P . ProductID = @ PID

Cease

When nosotros execute the above process without passing the parameter value, the default value 706 will be used. Just when executed passing the value, the default value will be ignored and the passed value will be considered as a parameter.

SQL Server stored procedures with default parameters

Creating a stored process with an output parameter

Below is the instance of a stored procedure with an output parameter. The following example retrieves the EmpID which is an car identity column when a new employee is inserted.

CREATE TABLE Employee ( EmpID int identity ( 1 , ane ) , EmpName varchar ( 500 ) )

CREATE PROCEDURE ins_NewEmp_with_outputparamaters

( @ Ename varchar ( l ) ,

@ EId int output )

Equally

BEGIN

Set up NOCOUNT ON

INSERT INTO Employee ( EmpName ) VALUES ( @ Ename )

SELECT @ EId = SCOPE_IDENTITY ( )

END

Executing the stored procedures with output parameters is bit different. We must declare the variable to shop the value returned by the output parameter.

declare @ EmpID INT

EXEC ins_NewEmp_with_outputparamaters 'Andrew' , @ EmpID OUTPUT

SELECT @ EmpID

SQL Server stored procedures with default parameters

inserted records in the table

Creating an encrypted stored procedure

We can hide the source code in the stored process by creating the procedure with the "ENCRYPTION" option.

Following is the example of an encrypted stored procedure.

CREATE PROCEDURE GetEmployees

WITH ENCRYPTION

Begin

SET NOCOUNT ON

SELECT EmpID , EmpName from Employee

END

When we try to view the code of the SQL Server stored procedure using sp_helptext, information technology returns "The text for object 'GetEmployees' is encrypted."

encrypted SQL Server stored procedures

When you endeavour to script the encrypted stored process from SQL Server direction studio, it throws an error as below.

encrypted SQL Server stored procedures

Creating a temporary process

Similar the temporary table, we tin can create temporary procedures too. At that place are two types of temporary procedures, one is a local temporary stored process and some other one is a global temporary process.

These procedures are created in the tempdb database.

Local temporary SQL Server stored procedures: These are created with # as prefix and can be accessed but in the session where it created. This process is automatically dropped when the connexion is closed.

Post-obit is the example of creating a local temporary process.

CREATE PROCEDURE #Temp

BEGIN

Print 'Local temp procedure'

END

Global temporary SQL Server stored procedure: These procedures are created with ## as prefix and can be accessed on the other sessions as well. This procedure is automatically dropped when the connexion which is used to create the procedure is closed.

Below is the example of creating a global temporary procedure.

CREATE PROCEDURE ##TEMP

Equally

BEGIN

PRINT 'Global temp procedure'

END

Modifying the stored procedure

Use the ALTER Process statement to modify the existing stored procedure. Post-obit is the example of modifying the existing process.

ALTER PROCEDURE GetProductDesc

Brainstorm

Set up NOCOUNT ON

SELECT P . ProductID , P . ProductName , PD . ProductDescription FROM

Product P

INNER JOIN ProductDescription PD ON P . ProductID = PD . ProductID

Cease

Renaming the stored procedure

To rename a stored procedure using T-SQL, use organisation stored procedure sp_rename. Following is the instance that renames the procedure "GetProductDesc" to a new name "GetProductDesc_new".

sp_rename 'GetProductDesc' , 'GetProductDesc_new'

renaming a SQL Server stored procedure

Decision

In this article, nosotros explored SQL Server stored procedures with different examples. In case you lot have any questions, please feel costless to ask in the comment section below.

Author
Contempo Posts

SQL Server DBA, Developer with good experience in SQL Server administration, development, functioning tuning, monitoring, high availability and disaster recovery technologies