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.

1 2 3 four five 6 7 8 nine x 11 12 13 14 fifteen sixteen 17

CREATE Table Product ( ProductID INT , ProductName VARCHAR ( 100 ) ) Get CREATE Tabular array ProductDescription ( ProductID INT , ProductDescription VARCHAR ( 800 ) ) Go INSERT INTO Product VALUES ( 680 , 'HL Route Frame - Black, 58' ) , ( 706 , 'HL Road Frame - Red, 58' ) , ( 707 , 'Sport-100 Helmet, Red' ) GO 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.' ) GO

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 AS 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.

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.

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 ) AS 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.

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

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 AS 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."

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

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 AS 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 AS 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'

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

Source: https://www.sqlshack.com/sql-server-stored-procedures-for-beginners/

Posted by: krollevessureary.blogspot.com

Share this post