What are the advantages of using Stored Procedure?

In this post, I will explain advantages of using Stored Procedure in SQL Server.

A Stored Procedure is a group of SQL statements that have been created and stored in the database. A Stored Procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. If we modify a Stored Procedure all the clients will get the updated Stored Procedure.

Syntax of creating Stored Procedure-

CREATE PROCEDURE procedurename

@parameter datatype

AS

            Body of the stored procedure (SQL Statements)

Advantages of using Stored Procedure-

• Stored Procedure can reduce network traffic, boosting application performance.
• Stored Procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Stored Procedures help promote code reuse.
• Stored Procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored Procedures provide better security to your data.

Read More

Find Stored Procedures & Views Containing Text

In this post, I will explain how to find Stored Procedures & Views with containing text in SQL server database using SQL query.

To find Stored Procedures & Views which contains given text in SQL server we need to write a query using SYS.SQL_MODULES.

DECLARE @Parameter VARCHAR(100)   

SET @Parameter = 'employee'

SELECT * 

FROM SYS.SQL_MODULES

WHERE DEFINITION LIKE '%' + @Parameter + '%'

Read More

Filter or Search SQL Stored Procedure using SQL Query

In this post, I will explain how to filter Stored Procedure with containing text in the SQL Server database using SQL Query.  

To filter all stored procedures which contain given text in SQL server we need to write a query using SYS.PROCEDURES table.

DECLARE @Parameter VARCHAR(100)

SET @Parameter = 'employee'

SELECT * FROM SYS.PROCEDURES          

WHERE NAME LIKE '%'+ @PARAMETER +'%'          

ORDER BY MODIFY_DATE DESC  

Read More