Tag Archives: Stored Procedure

How to check if a Stored Procedure exists in Sql Server

Many a times we come across a scenario where we need to execute some code based on whether a Stored Procedure exists or not. There are different ways of identifying the Stored Procedure existence in Sql Server, in this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

To demonstrate these different approaches let us create a sample database with a Table and a Stored Procedure by the below script:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
CREATE TABLE dbo.Customers (CustId INT, Name NVARCHAR(50))
GO
CREATE PROCEDURE dbo.GetCustomers(@CustId AS INT)
AS
BEGIN
	SELECT * FROM  dbo.Customers WHERE CustId = @CustId
END
GO

[ALSO READ] How to check if a Table exists in Sql Server

Approach 1: Using sys.procedures catalog view

We can write a query like below to check if a GetCustomers Stored Procedure exists in the current database in any schema.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'GetCustomers')
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures

The above query checks the existence of the GetCustomers Stored Procedure across all the schemas in the current database. Instead of this if you want to check the existence of the Stored Procedure in a specified Schema then we can re-write the above query as below:

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE object_id = OBJECT_ID(N'dbo.GetCustomers'))
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures2

If you want to check the existence of a stored procedure in a database other than the current contextual database, then we can use the script like below:

USE MASTER
GO
IF EXISTS(SELECT 1 FROM SqlHintsDemoDB.sys.procedures
 WHERE object_id=OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers'))
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures3

Note: sys.procedures catalog view contains a row for each object of the below type:

Type Description
P SQL Stored Procedure
PC Assembly (CLR) stored-procedure
RF Replication-filter-procedure
X Extended stored procedure

If you are looking for only Sql Stored Procedure, then in sys.procedures catalog views query you can add the filter AND condition as: Type = N’P’.

[ALSO READ] How to check if a record exists in table

Approach 2: Using sys.objects catalog view

sys.procedures catalog view inherits the rows from the sys.objects catalog view, sys.objects catalog view is referred to as base view where as sys.procedures is referred to as derived view. sys.procedures will return the rows only for the stored procedures whereas sys.objects view apart from returning the rows for stored procedures, it returns rows for the objects like: tables, views etc.

We can write a script like below to check the existence of a stored procedure in the current contextual database:

IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'dbo.GetCustomers')
                    AND type IN ( N'P', N'PC',N'X',N'RF')) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.objects

If you want check the existence of a stored procedure in a database other than the contextual database then we can re-write the above query by using three part naming convention as shown below:

USE master
GO
IF EXISTS (SELECT * FROM SqlHintsDemoDB.sys.objects
 WHERE object_id=OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers')
      AND type IN ( N'P', N'PC',N'X',N'RF')) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.objects2

[ALSO READ] How to check if a Temp table exists

Approach 3: Using sys.sql_modules Catalog View

We can use the sys.sql_modules catalog view to check the existence of the Stored Procedure as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM sys.sql_modules
   WHERE object_id =  OBJECT_ID(N'dbo.GetCustomers') 
   AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT
Check Stored Procedure Existence using sys.sql_modules

[ALSO READ] How to check if a Database exists

Approach 4: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if a GetCustomers Stored procedure exists in the current database.

USE SqlHintsDemoDB
GO
IF OBJECT_ID(N'dbo.GetCustomers', N'P') IS NOT NULL
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using OBJECT_ID function

Specifying the Database Name and Schema Name parts for the Stored Procedure Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the stored procedure in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the GetCustomers stored procedure in the dbo schema in the SqlHintsDemoDB database.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers', N'P')
     IS NOT NULL
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using OBJECT_ID function2

[ALSO READ] How to check if a View exists

Approach 5: Using INFORMATION_SCHEMA.ROUTINES View

We can use the INFORMATION_SCHEMA.ROUTINES view to check the existence of the stored procedure as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'GetCustomers' 
        AND ROUTINE_TYPE = 'PROCEDURE') 
BEGIN
    PRINT 'Stored Procedure Exists'
END

RESULT
Check Stored Procedure Existence using INFORMATION_SCHEMA.ROUTINES

Approach 6: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.procedures/sys.sql_modules instead of sys.sysobjects system table directly to check the existence of the stored procedure.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.sysobjects  
     WHERE id = OBJECT_ID(N'dbo.GetCustomers') AND xtype=N'P' )
BEGIN
    PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.sysobjects

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a View exists
How to check if Temp table exists
How to check if a record exists in table

How to Insert Stored Procedure result into a table in Sql Server?

Today, I was needed to dump the Stored Procedure result into a temp table.  Below example illustrates how this can be achieved:

First let us create a stored stored procedure which returns result set:

CREATE DATABASE DEMOSQLHINTS
GO
USE DEMOSQLHINTS
GO
CREATE TABLE dbo.Employee(ID INT IDENTITY(1,1),Name VARCHAR(50))

INSERT INTO dbo.Employee(Name)
VALUES('BASAVARAJ BIRADAR'),
('SHREE BIRADAR')
GO

CREATE PROCEDURE dbo.GetEmployees
AS
BEGIN
SELECT * FROM dbo.Employee WITH(NOLOCK)
END
GO
EXEC dbo.GetEmployees
GO

Result:

ID Name
———– ————————————
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR

(2 row(s) affected)

Below script shows how to move the result of the SP GetEmployees to a temporary table #TempEmployees

-- First create the table to which we want to push the SP result
CREATE TABLE #TempEmployees(EmpId int,EmpName Varchar(50))
GO
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.GetEmployees
GO
--Verify the Insert records
SELECT * FROM #TempEmployees

Result:

EmpId EmpName
———– ————————————————–
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR

Let us see another example where we Insert result from a Stored Procedure with Parameter into a temporary table

--First create a stored procedure with parameter
CREATE PROCEDURE GetEmployeeDetails
(@id AS INT)
AS
BEGIN
SELECT * FROM dbo.Employee (NOLOCK)
WHERE Id = @id
END
GO
--Execute SP with parameter and dump the result into a temp table
INSERT INTO #TempEmployees
EXEC GetEmployeeDetails 2

GO
-- Verify the inserted records
SELECT * FROM #TempEmployees


Result:

EmpId EmpName
———– ————————-
2 SHREE BIRADAR

Note: All the examples in this article are tested on Sql Server 2008 version

Difference between Stored Procedure and User Defined Function in Sql Server

Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.

To know more on the User-Defined functions with examples please visit the article: User-Defined function.
To know more on the Stored Procedure with examples please visit the articleStored Procedure.

Sl. No. User Defined function Stored Procedure
1 Function must return a value. Stored procedure may or not return values.
2 Will allow only Select statement, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete
etc
3 It will allow only input parameters, doesn’t support output parameters. It can have both input and output parameters.
4 It will not allow us to use try-catch blocks. For exception handling we can use try catch blocks.
5 Transactions are not allowed within functions. Can use transactions within Stored procefures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables aswell as temporary table in it.
7 Stored procedures can’t be called from function. Stored Procedures can call functions.
8 Functions can be called from select statement. Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec
statement can be used to call/execute stored procedure.
9 UDF can be used in join clause as a result set. Procedures can’t be used in Join clause

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Char Vs Varchar
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

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

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_DEFINITIONSo 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

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