What is a stored procedure in sql server

Are you new to SQL Server? Stored procedures are very important in SQL Server. In this tutorial, we will learn what is a stored procedure in SQL Server? How to create a stored procedure in SQL server 2019, Advantages of using SQL Server stored procedure. Also, we will check how to execute and alter a stored procedure in the SQL server.

If you have not yet installed SQL Server, first install SQL Server 2019.

What is a stored procedure in SQL Server?

Sometimes, there are scenarios when you want to execute some queries that you are going to use in various sections of SQL code. This is the time when stored procedures come in handy.

A stored procedure in SQL Server is a set of SQL queries that you can use again and again. You can create your own set of SQL queries and then store it as a procedure. You can execute that stored procedure by just calling it with a single line of code.

You can also manipulate data inside a stored procedure and then use that manipulated data.

A stored procedure is very similar to a programming language as it supports many programming features. For instance, you can create variables, pass parameters to the stored procedure, return output, and much more.

Stored procedure advantages

• The biggest advantage of a sql server stored procedure is its reusability. Once we have created a stored procedure containing multiple queries, we can execute it with a single line of SQL statement again and again. It means we do not have to write the full set of SQL queries again.
• You get a number of programming features with stored procedures.
• Stored procedures are compiled once and stored. It means we do not have to compile it again and again as in the case of writing the queries manually. As a result, stored procedures provide better performance.
• Stored procedures in SQL Server 2019 reduces network traffic. You can create a stored procedure on the server and make an execution call from a remote client machine instead of sending the whole set of SQL queries from the client machine over a network. This feature is more beneficial when you have hundreds of lines of code.
• The modular approach of the stored procedure makes it easy to use and maintain.
• Stored procedures provide security. You can use the authorization mechanism to grant user privileges.

Read How to create a table in sql server management studio

How to create a stored procedure in SQL Server 2019

Creating a stored procedure in sql server 2019 using management studio is a very easy task. We create a stored procedure using the CREATE PROCEDURE statement. Take a look at the syntax:

CREATE PROCEDURE <procedure name> <parameters list>
AS
-- Write SQL queries and statements in this block--
GO

• You can define parameters in a stored procedure followed by the data type of the parameter.
• After the AS keyword, you can write the SQL statements that want to include in the stored procedure.
• GO keyword is used to initiate the compilation.
• For example:

CREATE PROCEDURE PrintString
AS
PRINT('Hello! My name is Kushal Sharma')
GO

• In the above example, the name of the stored procedure is PrintString.
• This procedure prints the string ‘Hello! My name is Kushal Sharma’.

Read: SQL Server Substring Function

How to execute a SQL Server stored procedure

Earlier we learned how to create a stored procedure in SQL Server. Now we will learn how to execute the stored procedure using SQL server management studio.

• We execute a stored procedure with the EXEC keyword.
• The syntax for executing a stored procedure is:

EXEC <procedure name> <arguments list>
GO

• For example, in the above section, we created a stored procedure that prints a simple string ‘Hello! My name is Kushal Sharma’

CREATE PROCEDURE PrintString
AS
PRINT('Hello! My name is Kushal Sharma')
GO

• Now we will execute it

EXEC PrintString
GO

This is how we can execute a stored procedure in sql server 2019.

Read: How to export data from SQL Server to Excel

How to alter a stored procedure in SQL Server

If you ever wish to alter or modify your previously created SQL server stored procedure then, you can do it by using the ALTER statement.

• The ALTER statement is very similar to the CREATE statement. The difference is that the ALTER statement works with the existing procedure but the CREATE statement works with new stored procedure
• We have created a stored procedure as

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

• If you want to modify the procedure, just replace ALTER with CREATE and define the schema of the stored procedure

ALTER PROCEDURE PrintInput @String1 nchar(30), @String2 nchar(30)
AS
PRINT(@String1)
PRINT(@String2)

• Now let us execute it and see if it is working as expected

EXEC PrintInput 'Great!',
 'Stored Procedure Altered'

• As you can see, we got the desired result. We have learned how to alter a stored procedure in SQL Server 2019.

Read SQL Server stored procedure vs function

How to rename a stored procedure in SQL Server

In this section, you will learn how to rename a stored procedure in SQL Server 2019.

• We can rename a stored procedure in SQL Server.
• For doing this, we have to use a System Defined Stored Procedure which is sp_rename
• sp_rename takes two input parameters
  • Name of the existing stored procedure whose name you want to change
  • The new name you want to give to the stored procedure
• Suppose we have a stored procedure name as PrintInput.
• If we want to rename it as NewPrintInput, we will write the following query:

EXEC sp_rename 'PrintInput', 'NewPrintInput'

• If you check the list of Stored Procedures in the SQL Server Management Studio after refreshing it, you will find that the stored procedure has been renamed.
• You can check the list of stored procedures in the Object Explorer Window.

Read: How to execute stored procedure in SQL Server

SQL Server stored procedure best practices

Here, let us check out a few best practices of SQL server stored procedures.

• You should write the procedure with fully qualified names. For example, CREATE PROCEDURE [dbo].PrintString.This helps to identify where the procedure is stored in the database.
• Use comments within the procedure. This helps to improve the readability of the code. Also, it becomes easy to debug the code.
• You should use proper indentation while writing the code to improve the readability of the code.
• Use less number of variables because variables occupy space in memory. Using more variables will decrease the performance’.
• Try to avoid naming your stored procedure starting from sp_. Stored procedure name starting from sp_ tells the SQL Database Engine that it is a System Defined Stored Procedure and it starts to find it in the master database first which, in turn, will degrade the performance.
• Do not use * every time in the SELECT statement. Try to use the column names whose value you want to retrieve. It helps to understand what particular data will be fetched from the database.
• Prefer to use the SET NOCOUNT ON statement as using this statement will stop giving the default output messages. For example, if you have used the SELECT statement, you might have noticed that it returns an output message showing the affected number of rows. These default output messages can interfere with the output that you are desiring.
• Try to declare all the variables at the beginning of the stored procedure.
• Write keywords in all capital letters. This helps to identify parts of code easily.
• Avoid using the temp table in the database. The SQL Server Database engine stores compiled statements as cache in this table. If you modify this table, the table will be compiled again affecting the performance.
• Use the keywords like ORDER BY, GROUP BY, TOP, DISTINCT as least as possible because the SQL Server Database Engine first retrieves all the specified rows and then applies these keywords.
• Use exception handling to handle errors at the time of execution because sometimes the program can terminate itself because of the error.
• Prefer the CASE statement over the nested IF-ELSE statement. The CASE statement maps with the condition at once, which is not the case with the IF-ELSE statement as it checks for every block until it finds the required condition.
• Use the WHILE LOOP instead of cursors because cursors require more memory.
• Be cautious while defining the data type of the variables. The data type of the parameter should be according to the data type of the data that you are retrieving from the table. Suppose you defined nchar(25) for the variable and trying to store nchar(30) value in it, retrieved from the table. This will throw an error.
• Select TOP 1 when you want to store a single value into a variable. Because sometimes, there might be some query that was supposed to return a single value but returned more than one value. In that case, the program will be terminated.
• Use a table variable only when the size of the resultset is small. If the size is large then it will be stored in the temp table in the temp database, making it slower.
• Try to use less dynamic queries. When you use dynamic queries in the program they will need to be compiled at the time when we call the variable containing the dynamic queries.
• Prefer to use output statement instead of table values when you have a single column result
• If you have some complex queries in your program, move them to the views. This helps to simplify the queries.

You may like the following SQL server tutorials:

• Loop in SQL Server stored procedure
• SQL Server stored procedure modified date
• Stored procedure for search functionality in SQL Server
• Try catch in SQL Server stored procedure with examples
• SQL Server stored procedure return value
• Could not find stored procedure in SQL Server
• How to get list of users in SQL Server
• SQL Server check user permissions on table

In this tutorial, we learned, what is a stored procedure in SQL server 2019 and the below points:

1. Stored procedure advantages
2. How to create a stored procedure in SQL Server 2019
3. How to execute a SQL Server stored procedure
4. How to alter a stored procedure in SQL Server
5. How to rename a stored procedure in SQL Server
6. SQL Server stored procedure best practices