Category Archives: Stored Procedure

How to find all the Stored Procedures having a given text in it?

Scripts, Sql Server, Stored Procedure, , ,

Recently, I was needed to search for all the Stored Procedures having a given text in its definition. So, as usual did the g 🙂 🙂 gling,  most of the top results returned were suggesting to use INFORMATION_SCHEMA.ROUTINES view like below, which is not going to return correct result in all the scenario’s:

Below Script gives Wrong Result:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SearchString%'
AND ROUTINE_TYPE='PROCEDURE'

As usual tried to understand the query and thought of checking it’s correctness. And to my surprise when I checked this view’s definition using: sp_helptext ‘INFORMATION_SCHEMA.ROUTINES’,  the ROUTINE_DEFINITION column was returning only the first 4000 characters of the Stored Procedure definition  i.e. in view ROUTINE_DEFINITION column is defined as: convert(nvarchar(4000),object_definition(o.object_id))AS ROUTINE_DEFINITION. So with this it is clear that it will not return all the Stored Procedure’s which have the first occurrence of the search sting in its definition after 4000 characters.

 To get correct results we can use sys.procedures view as below, as the return type of the function OBJECT_DEFINITION(object_id) which returns Stored Procedure definition is of type nvarchar(max):

Below script gives Correct Result:

SELECT OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'

Below screen shot illustrates the difference between these two queries:

There are multiple alternative way’s with which we can correctly find all the Stored Procedure’s having a given text. And sys.procedures explained in this article is one such solution.

Please correct me, if my understanding is wrong. Comments are always welcome.

Stored Procedure

Sql Server, Stored Procedure

SQL Server Stored Procedure

Overview:
A stored procedure is nothing more than Pre-Prepared SQL code that you save so you can reuse the code over and over. So if you think about a query that you write over and over, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure. In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure.

Following are the main reasons for using stored procedures:

  • Precompiled execution: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
  • Reduced client/server traffic: If network bandwidth is a concern in your environment, you’ll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
  • Efficient reuse of code and programming abstraction: Stored procedures can be used by multiple users and client programs. If you use them in a planned way, you’ll find the development cycle takes less time.
  • Enhanced security controls: You can grant users permission to execute a stored procedure.

Creating a Sample DataBase and Table with Sample data to Understand Stored Procedure Concepts:
By using the below script we are creating a Sample DataBase Named: StoredProcDemo. Then in this database we are creating Customer table and inserting five records in this table.

CREATE DATABASE StoredProcDemo
GO
USE StoredProcDemo
GO

Create Table Customer
(
CustomerID int Identity(1,1) NOT NULL,
Name Varchar(100) Not NULL,
DateOfBirth DateTime,
City Varchar(50),
State Varchar(50)
)
GO
INSERT INTO Customer
VALUES(‘Customer1′,’06/18/2000′,’Bangalore’,’Karnataka’)

INSERT INTO Customer
VALUES(‘Customer2′,’06/10/1972′,’Pune’,’Maharastra’)

INSERT INTO Customer
VALUES(‘Customer3′,’01/18/1975′,’Mysore’,’Karnataka’)

INSERT INTO Customer
VALUES(‘Customer4′,’06/06/1974′,’Chennai’,’TamilNadu’)

INSERT INTO Customer
VALUES(‘Customer5,’06/18/2001′,’Bangalore’,’Karnataka’)

GO

Creating a simple stored procedure:
In this example we are creating a Stored Procedure  GetAllCustomers, which returns all the customer details

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
Select * FROM CUSTOMER
END

Now we will get query in our mind i.e. How to execute stored procedure? Following are the two different ways we can execute/call a Stored Procedure:

GetAllCustomers
OR
Exec GetAllCustomers

To check the content of an existing stored procedure we can use the statement like below:
sp_helptext  GetAllCustomers

Stored procedure with parameters:
In the below example we are creating a stored procedure named GetCityCustomers which has @CityName as input parameter and this stored procedure returns all the customer in the given input city from the Customer table

CREATE PROCEDURE GetCityCustomers
@CityName varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END
GO

Following are the few different ways we can execute/call a Stored Procedure with Parameter:

EXEC GetCityCustomers ‘Bangalore’
–OR–
GetCityCustomers ‘Bangalore’
–OR–
GetCityCustomers @CityName = ‘Bangalore’

Default Parameter Values:
Try Executing the stored procedure GetCityCustomers without parameter value:
EXEC GetCityCustomers

What is the result? Yes we will get errors because we are not passing the parameter.

Now we will get question, whether we can create a stored procedure with parameter which can take default value for the parameter if it is not passed? Yes we can do it, let us see it with example.
With below statement we can modify existing stored procedure GetCityCustomers to make the parameter @CityName as optional. Now if user is not passing the parameter value, it will take BANGALORE as default value for the parameter @cityname.

ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = ‘BANGALORE’
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END

Now try executing the previous statement EXEC GetCityCustomers . This time it works, because if we are not passing the input parameter @CityName value it value take the default value BANGALORE mentioned in the Stored Procedure definition. If we are explicitly passing the  input parameter value then it will over-ride the default value.

Now modify the stored procedure to return all the customer details if we don’t pass the input parameter @CityName by the below statement:

ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = NULL
AS
BEGIN
SELECT *
FROM Customer
WHERE (@CityName IS NULL OR City = @CityName)
END

Now try executing the GetCityCustomers stored procedure with parameter and without parameter value:
EXEC GetCityCustomers
GO
EXEC GetCityCustomers ‘Mysore’
GO
EXEC GetCityCustomers ‘Bangalore’
GO

Store Procedure with Multiple Parameters:
In the below example we are creating a stored procedure named GetCustomersByCityAndState which accepts two input parameters: @CityName and @State returns all the customer details from Customer table based on these input parameters.

Create PROCEDURE GetCustomersByCityAndState
@CityName varchar(50),
@State Varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE State = @State AND City = @CityName
END

Following are the few different ways we can execute/call stored procedure with multiple input parameters:
EXEC GetCustomersByCityAndState ‘Bangalore’,’Karnataka’
–OR
EXEC GetCustomersByCityAndState @CityName = ‘Bangalore’,@State =’Karnataka’
–OR
EXEC GetCustomersByCityAndState @State =’Karnataka’,@CityName = ‘Bangalore’

How to Drop an Existing Stored Procedure:
With below statement we are dropping the stored procedure GetCustomersByCityAndState created in the previous section
DROP PROCEDURE GetCustomersByCityAndState

Stored procedure with OutPut Parameters:
Let us create a stored procedure GetNoOfCustomersByCity with input parameter @CityName and output parameter @NoofCustomers. Output parameter returns the count of the number  of customers in the customer table for the input city.

Create PROCEDURE GetNoOfCustomersByCity
@CityName varchar(50),
@NoofCustomers int OutPut
AS
BEGIN
SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName
END
GO

Below statement shows how to execute stored procedure with output parameter and also shows how we can read the output parameter value:
Declare @cnt INT
EXEC GetNoOfCustomersByCity ‘Bangalore’,@cnt OUTPUT
PRINT @cnt

RETURN Parameter:
Let us create a stored procedure GetNoOfCustomersByCityWithReturnParam with input parameter @CityName and returns the count of the number of customers in the customer table for the input city by the RETURN statement.

Create PROCEDURE GetNoOfCustomersByCityWithReturnParam
@CityName varchar(50)
AS
BEGIN
DECLARE @NoofCustomers INT

SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName

RETURN @NoofCustomers
END
GO

Below statement shows how to execute stored procedure and get the value returned by the RETURN statement. Here the variable @cnt holds the value returned by the Stored Procedure.

Declare @cnt as int
EXEC @cnt  = GetNoOfCustomersByCityWithReturnParam ‘Bangalore’
PRINT @cnt

Stored Procedure for Data Manipulation:
Let us create a Stored Procedure named AddCustomer, which inserts record in the Customer table with input values

Create PROCEDURE AddCustomer
@Name Varchar(50),
@CityName varchar(50),
@DOB DATETIME
AS
BEGIN
INSERT INTO CUSTOMER (NAME,City,DateOfBirth)
VALUES (@Name,@CityName,@DOB)
END
GO

Let us use the below statement to insert record in the Customer table
EXEC AddCustomer ‘Raj’,’Bangalore’,’10/23/1988′
GO