SQL Server create stored procedure (15 ways)

In this SQL Server tutorial, we will learn about Stored procedures in SQL Server and How to create a stored procedure in SQL Server.

As we proceed further, we will discuss more topics like input, output parameters in a stored procedure. We will also learn different methods of creating a stored procedure in SQL Server 2019.

Also, we will try different examples related to stored procedures in SQL Server. The detailed list of topics discussed in the article is given below.

• SQL Server create stored procedure
• SQL Server create stored procedure permission
• Create stored procedure SQL Server management studio
• Create stored procedure SQL Server syntax
• SQL Server create stored procedure if not exists else drop
• SQL Server create stored procedure with parameters
• SQL Server create stored procedure with default parameters
• Create stored procedure SQL Server with input parameters
• Create stored procedure SQL Server with input and output parameters
• SQL Server create stored procedure with recompile
• SQL Server create stored procedure without validation
• SQL Server create stored procedure with table parameter
• SQL Server create stored procedure in master database
• SQL Server create stored procedure in another database
• SQL Server create stored procedure in multiple databases
• SQL Server create stored procedure with variables
• SQL Server create stored procedure return value
• Create stored procedure SQL Server without parameters
• Create stored procedure with try-catch in SQL Server

SQL Server create stored procedure

A Stored procedure in SQL Server, is a collection of queries that are used to achieve some goal. It is a prepared SQL code that is compiled and stored in a database and can be reused multiple times. The primary goal of stored procedures is to hide direct SQL queries from the code and speed up database operations like select, update, and delete.

A SQL Server stored procedure is very much similar to procedures created in other languages. A stored procedure in SQL Server can perform the following task.

• A stored procedure can accept multiple input parameters and return multiple results using output parameters.
• A stored procedure can include programming statements used to perform operations on the database. It can also include executing other procedures statements.
• It returns the success or failure status value indicating the reason for failure.

Now, there are two ways to create a procedure in SQL Server, first by using Transact-SQL query. And the second method is to use the SQL Server Management Studio.

In this section, we will discuss the creation of a stored procedure using Transact-SQL. For this implementation, we can either use the query editor in SQL Server Management Studio, or we can use any of the supported command-line utilities like SQLCMD.

Create stored procedure SQL Server syntax

To create a stored procedure in SQL Server, we have to use the ‘CREATE PROCEDURE‘ command. Here is a complete syntax of creating a stored procedure in SQL Server using Transact-SQL code.

CREATE PROC|PROCEDURE [schema_name.] procedure_name

AS  
BEGIN 
   sql_statement [;] [ ...n ] 
END

• The schema_name in the above syntax is used to represent the schema to which a procedure belongs. The stored procedures are bound to the schema in the SQL Server. So, if we don’t specify the schema_name while creating a procedure, a user’s default schema will be automatically assigned to the procedure.
• The procedure_name in the syntax is used to represent the name of the procedure which we want to create. And the procedure name should be unique within the schema.
• The BEGIN and END keywords are optional to use while creating a procedure. But using these keywords is highly recommended as it represents the body of the procedure.

Now, after creating a procedure, the next thing is to execute the procedure in SQL Server. And for this, we can implement the following syntax.

EXEC [schema_name.] procedure_name;

For the execution, first, we have to use the EXEC clause. After this, we have to specify the procedure name with its schema.

Let’s understand the whole implementation with the help of an example.

CREATE PROCEDURE GetAllRecords
AS
BEGIN
  SELECT [id], [first_name], [gender] FROM dbo.SampleTable;
END
GO

In the above example, we are creating a procedure with the name “GetAllRecods“. And in the procedure, we are using the SELECT statement to fetch 3 columns from the sample table. Now, if we execute the above code, we will get the following success message.

Next, to execute the procedure, we are going to use the following syntax.

EXEC GetAllRecords

And after successfully executing the above procedure, we will get the following output.

Read How to view stored procedure in SQL Server

SQL Server create stored procedure permission

So, before creating a stored procedure in SQL Server, the particular login or role requires CREATE PROCEDURE permission in the database. And it also requires ALTER permission on the schema in which the procedure is being created.

Permissions in SQL Server are types of access that can be granted to specific securable. So, in this section, we will learn how to assign permission to create a procedure in SQL Server.

As discussed above, a user requires two permissions to create a stored procedure in SQL Server. First is a CREATE PROCEDURE permission and second is the ALTER ON SCHEMA permission. Now the simplest way to grant permission is to use the following query given below.

GRANT CREATE PROCEDURE TO user_name;
GRANT ALTER ON SCHEMA::[dbo] TO user_name;

In the query, we need to specify the username in place of user_name to whom we want to grant access.

For example, consider the following code given below.

GRANT CREATE PROCEDURE TO [sqlUser];
GRANT ALTER ON SCHEMA::[dbo] TO [sqlUser];

In the above example, we are granting access to the username “sqlUser” for creating a stored procedure.

Read SQL Server stored procedure if else

Create stored procedure SQL Server management studio

There are two ways of creating a stored procedure using SQL Server Management Studio. The first is by using the query editor to execute the Transact-SQL query for creating a procedure. The Transact-SQL code to create a procedure is already explained above.

And the second method is to use the GUI (graphical user interface) options available in SQL Server Management Studio to create a procedure.

In this section, we will discuss the GUI options and understand the steps that we need to follow in SQL Server Management Studio to create a procedure.

For this demonstration, follow the given steps illustrated below.

• First, connect to an instance of Database Engine using correct credentials.
• Now, in Object Explorer, expand the Database in which you want to create a procedure.
• Next, expand the Programmability directory, right-click the Stored procedure option and click on “Stored Procedure….”. It will open a empty template in query editor that we can use to create a procedure.

The template generated by the SQL Server will have the following sample code.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
        <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

• Now we easily create a procedure by updating the template according to our needs. And click on the “Execute” option. Here is a small example of an updated template.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	sqlserverguides
-- Create date: 19-07-2021
-- Description:	It will fetch all the female records
-- =============================================
CREATE PROCEDURE GetFemaleRecords 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [id], [first_name], [last_name], [gender] FROM dbo.SampleTable
END
GO

• The above example will fetch all the female records from the sample table.

• Now to execute the created store procedure, expand the “Stored Procedure” option.
• Next, right-click the procedure and click on “Execute Stored Procedure..” option.

This is how to create stored procedure using SQL Server management studio.

Read SQL Server stored procedure naming convention and best practices

SQL Server create stored procedure with parameters

The power of a stored procedure lies in the ability to have multiple parameters. A parameter is used to send and receive data between stored procedures and functions and the program or tool that invoked them.

A stored procedure in SQL Server can have two types of parameters. The first is an input parameter, and the second is an output parameter.

• An input parameter is used to pass data value from the caller to the stored procedure. And the data value can be used within the stored procedure.
• An output parameter is used to return data value from a stored procedure to the caller of that procedure.
• A stored procedure can also return an integer code to the caller. And the procedure will return 0 if we don’t set a specific value to be returned.

And, in this section, we will discuss both the implementation of creating a stored procedure with input and output parameters. And we will also implement some examples related to it.

Create stored procedure SQL Server with input parameters

An input parameter is mainly used to send data values from the caller to the procedure. And then, the data values can be used within the procedure for some processing. For example, fetching all the records from the table based upon department name passed as an input parameter or using multiple input parameters to insert records in a table.

Here is a general syntax that we can use to create a stored procedure with input parameters.

CREATE PROCEDURE procedure_name
( @parameter_1 datatype,       --input parameters
  @parameter_2 datatype,
  ...
)
AS
BEGIN
   SET NOCOUNT ON
   sql_statement 
END

Now let’s use the syntax to create a procedure with an input parameter. And for this implementation, we are going to use the following code given below.

CREATE PROCEDURE GetDeptRecords
( @DeptName VARCHAR(50) )    --input parameter
AS
BEGIN
SET NOCOUNT ON
 
SELECT [Name], [gender], [Department] FROM DeptTable
WHERE [Department]=@DeptName
 
END

In the above example, we are creating a procedure with one input parameter. And we are using this input parameter to fetch all the records of a department where the department name is equal to the passed input parameter.

Now while executing the procedure, we have to pass the value of the input parameter. In our example, we have created an input parameter of string data type so, we need to pass the string value.

EXEC GetDeptRecords 'Legal'

And, if we execute the above query, the stored procedure will return all the records where the department name is “Legal“.

This is how to create stored procedure in SQL Server with input parameters.

Create stored procedure SQL Server with input and output parameters

Other than the input parameter, a stored procedure can also have an output parameter. An output parameter is used to return data value from procedure to caller as a result. We can have multiple input as well as output parameters in one stored procedure.

To create an output parameter, we have to specify the OUT or OUTPUT keyword with the parameter in the procedure.

Here is a general syntax that we can use to create a stored procedure with output parameters.

CREATE PROCEDURE procedure_name
( @parameter_1 datatype,        --input parameter
  @parameter_2 datatype OUTPUT, --output parameter
  ...
)
AS
BEGIN
   SET NOCOUNT ON
   sql_statement 
END

For better understanding, let’s understand this implementation with the help of an example.

ALTER PROCEDURE GetDeptRecords
( 
  @DeptName VARCHAR(50),  --input parameter
  @Count INT OUTPUT	  --output parameter	
)
AS
BEGIN
SET NOCOUNT ON
 
SELECT [Name], [gender], [Department] FROM DeptTable
WHERE [Department]=@DeptName

SET @Count = @@ROWCOUNT
 
END

In the above example, we have created a procedure with one input and one out parameter. And in the procedure, we are fetching all the records based upon department using the input parameter. After this, we are using the output parameter to get the count of records returned by the SELECT statement.

Note- The @@ROWCOUNT is a system variable that returns the number of rows affected by the last executed statement.

Next, to execute the above procedure, we have to use the following query.

DECLARE @count INT

EXEC GetDeptRecords 'Legal', @count OUTPUT 

SELECT @count AS 'Total Records'

• First, we have to declare a variable count that will store the value returned by the output parameter.
• Next, we have provided the value of the input parameter in the execution statement.
• After this, we have specified the count variable as the value of the output parameter. It will save the value returned by the output parameter in the count variable.
• And then, we can easily use the count variable to display the result using the SELECT statement.

After successfully executing the above example, we will get the following result.

This is how to create stored procedure in SQL Server 2019 with input and output parameters.

Read SQL Server stored procedure insert into with examples

SQL Server create stored procedure with default parameters

Before creating a stored procedure with a default parameter, let’s understand the need for a default parameter.

For this, we are continuing our GetDeptRecords procedure that returns the records of a department based upon the input parameter. And it has the following code.

CREATE PROCEDURE GetDeptRecords
(
  @DeptName VARCHAR(50)  --input parameter
)
AS
BEGIN
SET NOCOUNT ON
 
SELECT [Name], [gender], [Department] FROM DeptTable
WHERE [Department]=@DeptName
 
END

And what if we don’t specify any value for the input parameter in the execution. The SQL Server will return the following error.

Now there can be many occurrences where either NULL or no value is specified to the procedure. To overcome such situations, we can use the default parameter in a stored procedure. The default parameters carry a default value that will be used when no value is specified.

The standard syntax used to create a procedure with a default parameter is given below.

CREATE PROCEDURE procedure_name
( @parameter_1 datatype = default_value,
  @parameter_2 datatype = default_value,
  ...
)
AS
BEGIN
   SET NOCOUNT ON
   sql_statement 
END

For demonstration, let’s update our GetDeptRecords procedure and assign a default value to it.

ALTER PROCEDURE GetDeptRecords
(
  @DeptName VARCHAR(50) = 'Legal'  
)
AS
BEGIN
SET NOCOUNT ON
 
SELECT [Name], [gender], [Department] FROM DeptTable
WHERE [Department]=@DeptName
 
END

So, now our procedure will return the records of the legal department when no value is specified in the execution.

This is how to create stored procedure with default parameters in sql server 2019 or sql server 2017.

Create stored procedure SQL Server without parameters

The parameters in a stored procedure of SQL Server are optional to use. We can also create a procedure without using any parameter. A stored procedure without any parameters is the simplest form of procedure, and these are generally used when we only require to return some result set.

For demonstration, consider the following example given below.

CREATE PROCEDURE GetDeptRecords
AS
BEGIN
SET NOCOUNT ON
 
SELECT TOP 5 [Name], [Department] FROM DeptTable
 
END

In the above example, we have created a simple procedure that will return the first five records of the name and department column from a table.

And after successfully executing the above procedure, we will get the following result.

This is how to create stored procedure in SQL Server without parameters.

Read SQL Server stored procedure output parameter

SQL Server create stored procedure with table parameter

While working with procedures, we might be stuck in a situation where we need to pass multiple rows of data to the procedure. To overcome such situations, we need to use table-valued parameters in a stored procedure.

So, in this section, we will understand how to create a table-valued parameter in a SQL Server stored procedure.

To pass the table-valued parameter to a stored procedure, we have to follow the following steps.

• First, create a user-defined table type according to the requirements.
• Next, pass the user-define table type as a parameter to the stored procedure.
• Now, inside the procedure, process the data as per needs.

So, let’s understand how to create a user-defined table type in SQL Server. A user-defined table type is a predefined table used to store temporary data. And similar to a table, we can define data types and their properties using table type.

Here is a standard syntax used to create a user-defined table type in SQL Server.

CREATE TYPE type_name AS TABLE
(
    column_1 datatype,
    column_2 datatype,

    ...
)
Go

For demonstration, consider the following example used to create a user-defined table type.

CREATE TYPE EmpTableType AS TABLE
(
    [id]  INT PRIMARY KEY,
    [Name] VARCHAR(50),
    [Department] VARCHAR(50)
)
Go

In the above example, we have created a table type with 3 columns, similar to creating a table. And we are going to use this table further in our example.

So, after creating a user-defined table type, let’s create a stored procedure and try to pass the table type as a parameter to a stored procedure.

For this demonstration, we are going to execute the following code given below.

CREATE PROCEDURE InsertEmpRecords
@EmpType EmpTableType READONLY
AS
BEGIN
    INSERT INTO EmpTable
    SELECT * FROM @EmpType
END

In the example, we have created a procedure that accepts EmpTableType as a parameter. And within the procedure, we are using the table-valued parameter to select and insert all the records in the EmpTable table. It is important to use the READONLY clause with the parameter else it will raise a runtime error.

The final step is to create an EmpTableType variable and populate it with some data. And then, pass it to the InsertEmpRecords stored procedure. For this, we are going to execute the following script.

DECLARE @EmpRecord EmpTableType

INSERT INTO @EmpRecord VALUES (1, 'Richie', 'Engineering')
INSERT INTO @EmpRecord VALUES (2, 'Adella', 'Marketing')
INSERT INTO @EmpRecord VALUES (3, 'Terence', 'Sales')
INSERT INTO @EmpRecord VALUES (4, 'Carlen', 'Training')
INSERT INTO @EmpRecord VALUES (5, 'Liv', 'Legal')

EXECUTE InsertEmpRecords @EmpRecord

In the script, we have declared a variable of EmpTableType type. And then, we are inserting some values in the table-valued variable. In the end, we are passing the table-valued parameter to the stored procedure. Now, if we query the EmpTable table, we will get the following output.

After successfully implementing the above steps, we had created a procedure with a table-valued parameter.

This is how to create stored procedure with table parameter in sql server 2019.

Read PostgreSQL DATE Functions with Examples

SQL Server create stored procedure with recompile

In this section, we will learn how to create a stored procedure with recompile in SQL Server, But before creating the procedure, let’s understand the need to recompile a stored procedure in SQL Server.

• Whenever a procedure is compiled for the first time, the procedures query plan is optimized according to the current database state. Now, what if the database undergoes some changes in terms of data or structure. In such cases, recompilation updates the procedures query plan and improves the procedure performance.
• Procedures can be recompiled at the statement level in SQL Server. When SQL Server recompiles stored procedures, it only compiles the statement that prompted the recompilation, not the entire procedure.
• If certain queries within the stored procedure uses temporary values frequently then recompilation can imporve the performance of the procedure.

We can easily create a procedure with the recompile option by using the following syntax.

CREATE PROCEDURE procedure_name
(
  parameter_1 datatype,
  ...
)
WITH RECOMPILE  
AS
BEGIN
    SET NOCOUNT ON;  
    sql_statement
END

For demonstration, consider the following example illustrated below.

CREATE PROCEDURE dbo.uspDeptRecords
( @DeptName VARCHAR(50) )
WITH RECOMPILE  
AS
BEGIN
    SET NOCOUNT ON;  
    SELECT [Name] AS 'Employee Name', 
	   [Department] AS 'Department Name' FROM [DeptTable]
    WHERE [Department] = @DeptName
END

In the above example, we have created a procedure that returns the record of a table based upon the department name passed as an input parameter. And we have also specified the RECOMPILE clause in the procedure. It will recompile the procedure each time when it is executed.

SQL Server create stored procedure without validation

When we create a stored procedure in SQL Server, the SQL Server compiles the procedure and creates an execution plan for it. It simply means that all objects in the procedure are validated. The execution plan remains valid as long as the procedure does not change.

The SQL Server will not create the execution plan for the procedures that refers to a non-existing table object. However, the procedure can be created if no other errors are identified.

For demonstration, we are going to create a stored procedure that uses a non-existing table.

CREATE PROC sample_usp
AS
BEGIN
SELECT [id], [name], [age]  FROM [nonexistingTable]
END

And if we execute the above script, the procedure will be created successfully

This is how to create stored procedure without validation in sql server.

Read PostgreSQL ADD COLUMN + 17 Examples

SQL server create stored procedure with variables

A variable in SQL Server is an object that can store a single value of a specific data type. For example, date, integer, varying character. To use a variable in SQL Server first, we need to declare it, and then we can use it to assign some value.

And to declare a variable in SQL Server, we can use the following syntax.

DECLARE @variable_name datatype;

To declare a variable, we have to use the DECLARE statement which helps to initialize the variable with a variable name and data type. Also, a variable name should always start with a “@” sign. And when a variable is declared, its value is set to NULL by default.

A variable can be used in many places, one common usage of a variable is to store values returned by a stored procedure. So in this section, we will understand how to create a stored procedure with variables.

For demonstration, consider the following example given below.

CREATE  PROC uspGetEmpList
AS 
BEGIN
    DECLARE @emp_list VARCHAR(MAX);
    SET @emp_list = '';

    SELECT @emp_list = @emp_list + [Name] + ' is in "' + [Department]  + '" department' 
	               + CHAR(10)
    FROM [DeptTable]
    
    PRINT @emp_list;
END;

In the above example, we created a stored procedure that returns the name and department of all the employees. And for this implementation, we are using the variable to store the name and department string. Now, if we execute the procedure, we will get the following result.

This is how to create stored procedure with variables in sql server 2019.

SQL server create stored procedure return value

There are mainly 2 methods through which we can return a value from the stored procedures. The first is by using the output parameter and the second method is by using the return code.

So, creating a stored procedure with output parameters is already explained in the previous topic. Now, in this section, we will learn to create a stored procedure that returns values using return code.

A stored procedure in SQL Server can return integer values to the caller. These integer values are called return codes, and these codes are used to indicate the execution status of a procedure. And by default, the value for successful execution of the procedure is 0.

To specify a return code, we have to use the RETURN statement. And we also need to store the return code in a variable at the time of execution.

For example, consider the following script given below.

CREATE PROCEDURE [dbo].[GetAllRecords]
AS
BEGIN
	SELECT [id], [first_name], [gender] FROM dbo.SampleTable;
END
GO

In the above example, we have created a simple procedure, and it fetches all the records from a table.

Next, to get the return code, first, we have to declare an integer variable. After this, we have to use the variable in the execution statement. And then, we can easily use the variable to get the return code. The code to implement this is given below.

DECLARE @return_code INT
EXEC @return_code = GetAllRecords

SELECT @return_code AS 'Return Code'

As our example is executed successfully, we will get a return code 0.

This is how to create stored procedure return value in sql server.

Read PostgreSQL vs SQL Server: Detailed Comparison

SQL Server create stored procedure in another database

In SQL Server, we can easily create a stored procedure in any specified database. For this implementation, we just need to use the “USE database” statement before creating the procedure. The “USE” statement is used to select any existing database from SQL Server.

For demonstration, consider the following syntax illustrated below.

USE database --specify the database name here
GO

CREATE PROC procedure_name  --creating procedure
AS
BEGIN
  sql_statement
END

Let’s understand the implementation with the help of an example.

USE sqlserverguides
GO

CREATE PROC SampleProc
AS
BEGIN
  SELECT [name], [Department] FROM [EmpTable]
END
GO

In the above example, we have used the “USE” statement to create the stored procedure in the sqlserverguides database.

This is how to create stored procedure in another database in sql server 2019/2017.

Create stored procedure with try-catch in SQL Server

The TRY-CATCH block in SQL Server is used for error handling for Transact-SQL. In SQL Server, error handling provides us control over the Transact-SQL code. So, when things go wrong, we have the opportunity to react and possibly rectify the situation.

A TRY block can include a series of Transact-SQL statements. If an error occurs in the TRY block, control is handed to a CATCH block, which contains another set of statements. And we can retrieve detailed information about the error that occurred by using the following functions inside the CATCH block:

Function	Description
ERROR_LINE()	It returns the line number on which the exception occurred.
ERROR_MESSAGE()	It returns the complete text of the generated error message.
ERROR_PROCEDURE()	It returns the name of the stored procedure or trigger where the error occurred.
ERROR_NUMBER()	It returns the number of the error that occurred.
ERROR_SEVERITY()	It returns the severity level of the error that occurred.
ERROR_STATE()	It returns the state number of the error that occurred.

Here is a standard syntax that we can use to implement the TRY-CATCH block in SQL Server.

BEGIN TRY  
   -- statements that may cause exceptions
END TRY 
BEGIN CATCH  
   -- statements that handle exception
END CATCH  

And we can also use the TRY-CATCH block within a stored procedure to handle errors. In fact, creating a stored procedure with TRY-CATCH block is considered to be a best practice related to procedures.

Now, let’s understand this implementation with the help of an example. And for this, consider the following script given below.

CREATE PROCEDURE [dbo].[uspDivision]
( @x int, @y int, @z int OUT )
AS
BEGIN
  BEGIN TRY
    SET @z = @x / @y
  END TRY
  BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
	   ERROR_SEVERITY() AS ErrorSeverity, 
	   ERROR_STATE() AS ErrorState,
	   ERROR_PROCEDURE() AS ErrorProcedure,
	   ERROR_LINE() AS ErrorLine,
	   ERROR_MESSAGE() AS ErrorMessage;
  END CATCH
END
GO

In the above example, we have created a procedure that will calculate the division between 2 integer variables. And after calculation, it will return the result as an output parameter. In the procedure, we have put the calculation part in the TRY block, and for the error handling, we are using the error detail function in the CATCH block. So, if there is some error while division then, the function in the CATCH block will be executed first.

For demonstration, let’s encounter an error by dividing some integer with zero. In this case, the procedure will return the following result.

This is how to create stored procedure with try-catch in SQL Server 2019/2017.

SQL Server create stored procedure in multiple databases

In this section, we will learn how to create a stored procedure in multiple SQL Server databases.

Now, creating stored procedures in multiple databases is a tricky task. One of the ways to implement this is by using the sp_MSforeachdb procedure. The sp_MSforeachdb is a system procedure in SQL Server that executes the same task for every database in the server.

Here is a simple example of using the sp_MSforeachdb procedure to create a stored procedure in every database.

DECLARE @query VARCHAR(MAX)
SELECT @query = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
		BEGIN USE ? 
		EXEC(''CREATE PROCEDURE usp_GetRecords AS 
		SELECT * FROM DeptTable	
			
                     '') 
		END'

EXEC sp_MSforeachdb @query

In the example, first, we created a VARCHAR(MAX) variable with the name “@query“. After this, we are using this variable to create the SQL code to check the existence of a procedure and then, creating a procedure. In the end, we are executing the sp_MSforeachdb procedure with the “@query” variable as a parameter.

After implementing the above example, the sp_MSforeachdb procedure will create the procedure in all the databases of the SQL Server.

This is how to create stored procedure in multiple databases in sql server 2019.

SQL Server create stored procedure in master database

A master database in SQL Server can have 2 types of stored procedures. One is the system stored procedure and another is a user-defined stored procedure.

Now, Microsoft already provides a large set of system procedures that are used to query their system tables, and it helps to retrieve some information.

SQL Server also allows the user to create a stored procedure in the master database and use it as a system stored procedure. For this, we need to create a procedure with the sp_ prefix in the master database. If a stored procedure starts with the prefix sp_, the engine will look for it in the master database first, then examine the current database and ownership.

For the demonstration of creating a stored procedure in the master database, consider the following example.

USE MASTER 
GO

-- drop stored procedure if it exists
IF OBJECT_ID('sp_GetTableInfo') IS NOT NULL 
DROP PROCEDURE DBO.sp_GetTableInfo 
GO 

-- create stored procedure
CREATE PROCEDURE DBO.sp_GetTableInfo
( @table VARCHAR(MAX) )
AS 
   SET NOCOUNT ON 


   SELECT * 
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_NAME = @table 
GO 

In the above example, we have created a stored procedure “sp_GetTableInfo” in the master database. This procedure will try to find the table whose name is passed as a parameter. And if the table exists, the procedure will return the basics information related to it.

Now, before executing the procedure, we have to mark this procedure as a system stored procedure. For this, we have to implement the following query.

USE MASTER 
GO 

EXEC sp_ms_marksystemobject 'sp_GetTableInfo' 
GO 

In the query, we have used sp_ms_marksystemobject, which will tell the SQL Server engine to refer “ sp_GetTableInfo ” procedure as a system procedure. If we don’t execute this, then the procedure will act like a normal user-defined procedure.

Now, let’s try to execute the procedure from some other database. Here is the execution of this implementation.

This is how to create stored procedure in master database in sql server 2019.

Also Read: SQL Server stored procedure if exists update else insert

SQL Server create stored procedure if not exists else drop

While creating a stored procedure, we all might face a situation where we write the query to create a table, but the table with the same name already exists in the database. Such situations can be very irritating if we are using a command-line interface as we have to write the query again.

To overcome such issues, we can first check the existence of the procedure and delete it if it already exists, and then create a new procedure.

So, here is an example that first checks the existence of the procedure, and if the procedure already exists it will be dropped else it will create a new one.

IF OBJECT_ID('GetAllRecods', 'P') IS NOT NULL --checking the existance
DROP PROC GetAllRecods                        --deleting it
GO

CREATE PROC GetAllRecods                      --Creating a new procedure
AS
BEGIN
  SELECT * FROM DeptTable
END

In the above example, we have created a script that will first check the existence of a procedure GetAllRecords. And if the procedure already exists, the script will delete it. But if the procedure does not exist, then it will create a procedure with the same name.

This is how to create a stored procedure if not exists else drop in sql server.

So in this tutorial, we have learned how to create a stored procedure in SQL Server, and we have also covered the following topics.

• SQL Server create stored procedure
• SQL Server create stored procedure permission
• Create stored procedure SQL Server management studio
• Create stored procedure SQL Server syntax
• SQL Server create stored procedure if not exists else drop
• SQL Server create stored procedure with parameters
• SQL Server create stored procedure with default parameters
• Create stored procedure SQL Server with input parameters
• Create stored procedure SQL Server with input and output parameters
• SQL Server create stored procedure with recompile
• SQL Server create stored procedure without validation
• SQL Server create stored procedure with table parameter
• SQL Server create stored procedure in master database
• SQL Server create stored procedure in another database
• SQL Server create stored procedure in multiple databases
• SQL Server create stored procedure with variables
• SQL Server create stored procedure return value
• Create stored procedure SQL Server without parameters
• Create stored procedure with try-catch in SQL Server