Advanced Stored Procedure Examples in SQL Server (17 Examples)

In this tutorial, we will know about Advanced Stored Procedures in SQL Server. As we proceed further, we will see more advanced stored procedure examples in sql server.

We will demonstrate these examples on SQL Server 2019. Also, we will be using SQL Server Management Studio because it is easy to understand things in a GUI-based environment

We will start with the basic examples of Stored Procedures in the SQL server 2019 and further move to advanced stored procedure examples in sql server.

• Advanced SQL Server Stored Procedure Examples
• Create a stored procedure in SQL Server with input parameters
• Stored Procedure to insert data
• Stored Procedure to update table
• Stored procedure to select data from table
• Stored Procedure to delete data from table
• Stored Procedure to validate username and password
• Stored procedure in SQL to add two numbers
• Stored procedure in SQL with multiple queries
• Stored procedure for insert and update in SQL Server with output parameter
• SQL Server stored procedure to list columns
• Dynamic where clause in SQL Server stored procedure
• SQL Server stored procedure return select result concat
• Print 1 to 100 SQL Server stored procedure
• SQL Server stored procedure create directory
• Get servername in SQL Server stored procedure
• SQL Server stored procedure alter role
• SQL Server stored procedure to return the most recent record
• SQL Server stored procedure best practices

Advanced SQL Server Stored Procedure Examples

Here is a list of advanced SQL server stored procedure examples.

1. Stored Procedure to insert data

In this section, we will understand how SQL server stored procedures work, with the help of a basic insert query example.

• First of all, consider the following sql server 2019 table

• We will create a procedure to insert a row in the table.
• The query will be like below:

CREATE PROCEDURE insert_data @College_ID int, @First_name nchar(30), @Last_name nchar(30), @Stream nchar(25), @Email nchar(30), @Phone bigint
 AS
 INSERT INTO Student
         ([College ID], [First Name], [Last Name], Stream, [E mail], Phone)
 VALUES (@College_ID ,@First_name ,@Last_name ,@Stream, @Email, @Phone)
 GO

• If we understand the query, we have created a stored procedure with the name insert_data.
• We defined six parameters, along with the type of data they will hold.
• The procedure uses these parameters in the INSERT statement.
• As a result, the output will be:

• Once we have created the procedure, we can execute it with the EXEC statement
• We will pass the arguments into the procedure. The query looks like below:

EXEC insert_data @College_ID=1918, @First_name='Will', @Last_name='Smith', @Stream='Economics', @Email='willsmith@yahoo.com', @Phone=11305193063;
GO

• Once the query gets executed, you will see a new row.
• We successfully entered the data into the table

This is an example of sql server stored procedure to insert data.

2. Stored Procedure to update table

In this section, we have a table called Employees that contains information about the employees. We will update the salary of an employee with stored procedures.

• We will need a table first, on which, we will be implementing our procedure.

• We will create a stored procedure that will update the salary of an employee
• We have to pass Employee ID and Increment as arguments to the stored procedure
• In @Employee_ID we pass the ID of the employee whose salary we want to update
• In @Increment, we pass the amount by which we want to increase the salary

CREATE PROCEDURE update_salary @Employee_ID int, @Increment int 
AS

UPDATE Employees SET Salary=Salary+@Increment where [Employee ID]=@Employee_ID
GO

• Let us suppose if we want to increase the salary of an employee whose ID is 10 and by the amount 10000
• We will write the query as

EXEC update_salary @Employee_ID=10, @Increment=10000
GO

• You can see that the execution of the procedure updated the value in the table

• We can update the salary of an employee by just entering the Employee ID and the increment amount.
• But, keep in mind to pass values with the correct data type, as passing incorrect values will throw an error
• We do not need to write the full query again and again.

3. Stored procedure to select data from table

In this section, we will learn how we can create a stored procedure in SQL Server 2019 to select data from table.

• Take a look at the following table

• After we created this table, we created a stored procedure to select data from table

CREATE PROCEDURE SelectData @Department nchar(30)
AS
SELECT * FROM Employees WHERE Department=@Department
GO

• We created a stored procedure with the name SelectData and this will fetch the details of some employees
• The procedure will fetch the details of the employees whose Department value we will pass to the procedure during execution
• The @Department is the parameter to which we will pass the Department value
• Then we are using this value inside the SELECT statement as a WHERE condition
• When we execute this procedure, the result should look like as shown in the image

• Once created, it is time to execute the procedure
• To execute the procedure, write the following query in the Query Window
• You can specify a Department value, whose data you want to display

EXEC SelectData @Department='Marketing'
GO

Thus, we learned how to create a stored procedure to select data from table

4. Stored Procedure to delete data from table

In the database, there is always a need to delete some data. We can delete the data with the help of stored procedures. Let us see how:

• This example consists of a student table, named as Student as shown below in the image

• We will create a stored procedure that will delete the data of a particular student
• We will provide the College ID of the student to the stored procedure
• The stored procedure will delete the data of the student belonging to the provided College ID
• The stored procedure to delete data will look like

CREATE PROCEDURE DeleteData @CollegeID int
AS
DELETE FROM Student WHERE [College ID]=@CollegeID
GO

• After you executed the query, the following output is expected

• Remember that [College ID] is the attribute in the table and @CollegeID is the parameter of the stored procedure.
• Once the stored procedure is created, you can execute it.

EXEC DeleteData @CollegeID=1918
GO

• The data of the student with College ID as 1918 should not be there in the table. Let us check

At the end of this section, we know how to create a stored procedure in SQL Server 2019 to delete data from the table

5. Stored Procedure to validate username and password

Validating login credentials is one of the most common tasks that we need to perform in a database. In this example, we will create a small stored procedure in SQL server, that will help us to understand how validation works. We will create a stored procedure to validate username and password of a person

• Let us say we have a Login table in database that is storing login credentials of users

• We will create a stored procedure to vaildate username and password

CREATE PROCEDURE Verify @username nchar(20), @password nchar(20)
AS

IF EXISTS(SELECT * FROM Login where Username=@username and Password=@password)
	PRINT('User Exists')
ELSE
	PRINT('User Does not Exist')
GO

• Let us understand the code first
• There are two parameters in the stored procedure
  • @username
  • @password
• IF EXISTS checks whether the SELECT query returned some data or not
• It will print
  • ‘User Exists’ if the database contains the requested data
  • ‘User Does not Exist’ if the database does not contain the requested data
• Let us pass the incorrect Username and Password to the stored procedure

EXEC Verify @username='David', @password='David123'
GO

• Perfect! our code gave the expected output
• Now let us give the correct Username and Password to the stored procedure

EXEC Verify @username='Harry', @password='harry123'
GO;

• This time also, we got the expected output

Thus, in the above section, we learned how we can implement login validation. We also learned the usage of the IF EXISTS statement.

6. Stored procedure in SQL to add two numbers

In this example, we will understand how we can add two numbers using stored procedures in sql server 2019.

• We have created a stored procedure with the name addition.
• There are two parameters
  • @first_number of type Integer
  • @second_number of type Integer
• @sum is the variable that we have created to store the sum of two numbers.
• The PRINT statement will print the result.
• STR method will convert the integer value into a string.

CREATE PROCEDURE addition @first_number int, @second_number int
AS
DECLARE
@sum int
BEGIN
SET @sum=@first_number+@second_number
PRINT('Sum of given numbers is:'+STR(@sum))
END
GO

• Now let us execute the stored procedure.

EXEC addition 40, 60
GO

Thus, we created a stored procedure got the sum of two numbers i.e 40 + 60 = 100

7. Stored procedure in SQL with multiple queries

You can execute multiple queries in a stored procedure in SQL server 2019. For example, we will execute multiple SELECT queries on a table and store the values in different variables. Further, will print the output to the screen

• We have a table storing data of some users.

• We will create a stored procedure in SQL Server which will fetch information of the user from the table and give the output in a descriptive manner
• DisplayInfo is the name of the stored procedure having one argument @employee_id
• We will pass the Employee ID of an employee whose data we want to retrieve
• The data of the employee is stored in four variables using multiple SELECT queries
• Then, we used PRINT statement to display the information

CREATE PROCEDURE DisplayInfo @employee_id int
AS
DECLARE

@name nchar(30),
@department nchar(30),
@age int,
@salary real

BEGIN

SET @name= (SELECT Name FROM Employees WHERE [Employee ID]=@employee_id)
SET @department= (SELECT Department FROM Employees WHERE [Employee ID]=@employee_id)
SET @age= (SELECT Age FROM Employees WHERE [Employee ID]=@employee_id)
SET @salary= (SELECT Salary FROM Employees WHERE [Employee ID]=@employee_id)
PRINT('Employee Name is:'+ @name)
PRINT('Employee Department is:'+@department)
PRINT('Employee Age is:'+STR(@age))
PRINT('Employee Salay is:'+STR(@salary))

END
GO

• Once we have created this stored procedure, we can execute it by providing the Employee ID as an argument
• We have provided the Employee ID as 3. As a result, the stored procedure will retrieve the information of an employee with the Employee ID 3

EXEC DisplayInfo 3
GO

So, we hope that after reading the above example, you might have learned about how you can create a stored procedure in SQL Server with multiple queries

8. Stored procedure for insert and update in SQL Server with output parameter

In this section, we will learn how to create a stored procedure for insert and update in SQL Server with output parameter

• We will use the following table

• We will create a stored procedure that will take the Employee ID of a person and checks if it is in the table. There are two conditions
  • It will create a new record if the Employee ID is not stored in the table
  • If the record is already in the table, it will update that

CREATE PROCEDURE UpdateEmployee @EmpID int, @Name nchar(50),
				@Department nchar(50), @Age int, @Salary real,
				@Message nchar(30) output
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT * FROM [DBO].[Employees] WHERE [Employee ID]=@EmpID)
	BEGIN
	SET @Message='Row Updated'
	UPDATE Employees
	SET [Name]= @Name,
	Department=@Department,
	Age=@Age,
	Salary=@Salary
	WHERE [Employee ID]=@EmpID
	END
ELSE
	BEGIN
	INSERT INTO Employees([Employee ID],[Name], [Department], [Age], [Salary])
	VALUES(@EmpID, @Name, @Department, @Age, @Salary)
	SET @Message='Row Inserted'
	END
END

• In this stored procedure @Message is the output parameter. It will return a message according to the condition
• Now, let us execute this stored procedure

DECLARE
@MessageReturned nchar(30)
BEGIN
EXEC UpdateEmployee 2, 'James','Finance',25, 100000, @MessageReturned OUTPUT

PRINT(@MessageReturned)
END

• @MessageReturned is a local variable in which we store the value of the output parameter and then print it
• This record is already stored in the table. Therefore, the message returned should be ‘Row Updated’

• This time we will give an Employee ID which is not stored in the table

DECLARE
@MessageReturned nchar(30)
BEGIN
EXEC UpdateEmployee 15, 'Smith','Marketing',26, 110000, @MessageReturned OUTPUT

PRINT(@MessageReturned)
END

• The returned message should be ‘Row Inserted’

Thus, in this example, we learned about output parameters. We learned how to create output parameters and how to use the output parameters in SQL server.

9. SQL Server stored procedure to list columns

A system-defined stored procedure named sp_columns in SQL Server gives the column and table information of a table. You just need to pass the table name as an input parameter to the procedure and you will get the whole information about the table and its columns. You can execute this procedure as:

EXEC sp_columns <table name>

• For example, we have a table named Persons. If we want its columns’ information, we will use the stored procedure as:

EXEC sp_columns 'Persons'

Using Your own custom stored procedure:

You can also create your own custom information to get only some specific information about the columns of a table. You may know that there is a view in SQL Server named INFORMATION_SCHEMA from where you can retrieve the schema information.

We will use this view to retrieve the list of columns of a table. We will create a stored procedure and use this logic inside the stored procedure.

USE master
GO
CREATE PROCEDURE dbo.ListColumns @TableName nchar(128)
AS
BEGIN
	SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @TableName
END

• We are just retrieving the table name, column name and the data type of the column name. You can retrieve more columns if you want.
• We will execute this stored procedure and pass the name of a table that we have created as the input parameter.

USE master
GO
EXEC dbo.ListColumns 'Persons'

Thus, you might have known how you can retrieve the list of columns using a stored procedure.

10. Dynamic where clause in SQL Server stored procedure

In this section, you will learn about dynamic SQL queries. We will create an example where we will use the dynamic SQL query in the WHERE clause of a Select statement in SQL Server.

We use the dynamic queries when we want to specify a value for checking against a condition, but the value is not static i.e. we will dynamically take that value. Dynamically means that the value will be defined at the run time instead of compile time.

We have created the following example of a stored procedure that will search the items in a table on the basis of input parameters provided to it. We have used dynamic queries to search for the data in the table.

USE [master]
GO
CREATE PROCEDURE [dbo].[SearchPersons] @FirstName varchar(20)= NULL, @Phone varchar(20)= NULL
AS
BEGIN
	SET QUOTED_IDENTIFIER ON
	DECLARE
		@sql nvarchar(80)
		SET @sql = 'SELECT * FROM dbo.Persons WHERE '
			IF @FirstName IS NOT NULL AND @Phone IS NOT NULL
	BEGIN
		SET @sql= CONCAT(@sql, '[First Name]=','''', @FirstName, '''',' AND Phone=' ,'''', @Phone,'''')
		PRINT(@sql)
	END
	ELSE 
	BEGIN
		IF @FirstName IS NULL
		BEGIN
			SET @sql= @sql + ' Phone=' + ''''+ @Phone+''''
		END
		ELSE
		BEGIN
			SET @sql= @sql + '[First Name]='+''''+ @FirstName+''''

		END
	END
	EXEC(@sql)
END

• The code looks complex, but don’t worry you will understand once we explain it to you.
• The stored procedure can take one or two or both parameters and use these parameter values in the Select query to return the corresponding resultset. The NULL value is the default value for both parameters.
• We used the SET QUOTED_IDENTIFIER ON option to escape the single quotes in our dynamic query. Escaping the quotes means using a single quote inside a string.
• Then we created a variable @sql and stored the Select query as a string inside the variable.
• Based on the conditions we decided the value for the Where clause and concatenated the string for the Where condition with the @sql variable.
• Then we executed this dynamic query with the EXEC() function.

You can observe in the above example that we could manipulate the Where condition dynamically, based on our requirements. Thus, you might have understood how you can use a dynamic Where clause in a stored procedure.

11. SQL Server stored procedure return select result concat

In this section, you will see an example of a stored procedure that will return a concatenated result of two columns of a Select statement. You will see how you can concatenate the results of a Select statement.

• We have a table named Persons.
• The table has two columns:
  • First Name
  • Last Name
• Suppose you have not created a column for Full Name and want to retrieve full names of people in a single column. You can concatenate the resullts of the given two columns. Let us see how:

USE master
GO
CREATE PROCEDURE dbo.ReturnFullName @ID int
AS
BEGIN
	SELECT [First Name] + [Last Name] AS [Full Name]
	FROM dbo.Persons WHERE ID= @ID
END

• The above stored procedure will accept the ID of a person as an input parameter and return the Full Name of the person with the corresponding ID.
• We will execute the stored procedure as:

USE master
GO
EXEC dbo.ReturnFullName  12

• As you saw in the output, we got the concatenated result of two columns in a single column.

Thus, you might have learned how you can concatenate the results of different columns of a Select statement resultset.

12. Print 1 to 100 SQL Server stored procedure

In this section, you will see an example of a stored procedure that will print all the integers from 1 to 100. Printing all the numbers manually is obviously not a good approach. Therefore, we will use a WHILE loop to do this. The following SQL code will create the stored procedure:

USE DemoDB
GO
CREATE PROCEDURE dbo.Print100
AS
BEGIN
	DECLARE
		@Num int= 1
	WHILE @Num <= 100
	BEGIN
		PRINT(@Num)
		SET @Num = @Num + 1
	END
END

• We have created a variable and set its value to 1.
• Then we have defined the condition after the WHILE keyword. The code inside the While loop block will execute till the condition is true.
• Then we are printing the number and incrementing its value by 1 every time the code inside the loop is run.
• When the value of @Num variable becomes 100, the condition will become false and the code inside the loop will not run. Consequently, the program will end and the numbers from 1 to 100 will be printed.
• Now let us try to execute the stored procedure:

USE DemoDB
GO
EXEC dbo.Print100

As a result, the numbers from 1 to 100 are printed on the output screen. Hence, you might have learned how you can use a While loop and in which type of scenarios it should be used.

13. SQL Server stored procedure create directory

We will create an example of a stored procedure in which we will create a directory in the local file system where the SQL Server instance is running. We will use the xp_cmdshell system stored procedure to create the directory. We use this stored procedure to run commands in the Windows command line. We will pass the command to create a new folder in the Windows command line through this xp_cmdshell statement.

Before writing the stored procedure, there are few things you have to keep in mind.

• You need to enable Show Advanced Options and xp_cmdshell.
• You need the necessary permissions for executing the xp_cmdshell.

To enable the Show Advanced Options and xp_cmdshell, write the following SQL script:

USE master
GO
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
GO

If there are enabled successfully, you will see an output like the below in the image:

Once we have enabled the necessary options, we can use the xp_cmdshell statement to run the commands on the Windows command line. To create a folder, pass the command to create a folder to this stored procedure as the parameter:

EXEC xp_cmdshell <Command to Create Folder>

• For example, If I want to create a folder at the location C:\Documents with the name MyFolder, I will write the command as:

EXEC xp_cmdshell 'MD C:\Documents\MyFolder'

• You can verify the if the folder is created or not by navigating to the same path in the Windows Explorer.

14. Get servername in SQL Server stored procedure

You can get the server name on which you are working with the @@SERVERNAME global variable. The following stored procedure will get the current name of the server:

USE master
GO
CREATE PROCEDURE dbo.GetServerName
AS
BEGIN
	PRINT(@@SERVERNAME)
END

• Executing the stored procedure will print the name of the SQL Server instance.

USE master
GO
EXEC dbo.GetServerName

15. SQL Server stored procedure alter role

We use the ALTER ROLE statement to alter the members of a role in the database. You can add, remove or rename the members of a database role. We will create a stored procedure that will rename a member name of a database role.

We have created a member named TestRole for a database role named dbo.

USE master
GO
CREATE ROLE TestRole AUTHORIZATION dbo;  
GO 

• Now we will create a stored procedure that will rename this member.

USE master
GO
CREATE PROCEDURE dbo.RenameMember 
AS
BEGIN
	ALTER ROLE TestRole
	WITH NAME = Member1
END

• After executing this stored procedure the member TestRole will be renamed as Member1.

USE master
GO
EXEC dbo.RenameMember

16. SQL Server stored procedure to return the most recent record

To return the most recent records from a table in SQL Server, you need a column that stores unique values in a sequence. For example, it can be an ID column or an Identity column, or a column that stores timestamp when the record is inserted.

Recording the timestamps at the time of record insertion is the best idea among all the above methods. You can perform a lot of operations and get a lot of information. For example, you can find the records inserted on a particular date or time. You can easily find the last inserted record if you have created a timestamp column.

However, in this section, we will see how we can find the last inserted record of a table having an identity column. We will create a stored procedure to find the last inserted record from a table.

• Consider the following table TableIdentity.

• The marked row is the last row that we have inserted into the table.
• We will create a stored procedure that will return this last inserted row.

USE master
GO
CREATE PROCEDURE dbo.FindLatest
AS
BEGIN
	SELECT TOP 1 * FROM dbo.TableIdentity
	ORDER BY EmpID DESC
END

• Let us execute the stored procedure:

USE master
Go
EXEC dbo.FindLatest

Tip: If you do not have a unique column, you can alter the table and create an identity column, but only if the size of the table is small. If you are dealing with a huge table then it is not a good approach as it will take a lot of time.

17. Create a stored procedure in SQL Server with input parameters

You can also create a stored procedure in SQL Server with input parameters. All you need to do is define an input parameter along with its data type.

• The following is a stored procedure in SQL server which takes a string as an input and prints it.

CREATE PROCEDURE PrintInput @InputString nchar(30)
AS
PRINT(@InputString)
GO

• This stored procedure has @InputString as a parameter of data type nchar(30).
• The PRINT statement prints the value that is passed to the stored procedure as an argument.
• Now, let us execute the stored procedure:

EXEC PrintInput 'Hello World'
GO

Hence, we learned how we can create a stored procedure in SQL Server with input parameters.

We created and executed various advanced Stored Procedures examples in SQL server 2019.

• Create stored procedure SQL in Server with input parameters
• Stored Procedure to insert data
• Stored Procedure to update table
• Stored procedure to select data from table
• Stored Procedure to delete data from table
• Stored Procedure to validate username and password
• Stored procedure in SQL to add two numbers
• Stored procedure in SQL with multiple queries
• Stored procedure for insert and update in SQL Server with output parameter
• SQL Server stored procedure to list columns
• Dynamic where clause in SQL Server stored procedure
• SQL Server stored procedure return select result concat
• Print 1 to 100 SQL Server stored procedure
• SQL Server stored procedure create directory
• Get servername in SQL Server stored procedure
• SQL Server stored procedure alter role
• SQL Server stored procedure to return the most recent record

You may also like:

• Optional Parameters in SQL Server Stored Procedure
• Find Stored Procedure by Name in SQL Server
• Stored procedure in SQL Server for insert and update
• Temp table in stored procedure in SQL Server
• Drop stored procedure SQL Server
• How to test stored procedure in SQL Server