How to create a subroutine in a stored procedure in SQL Server?

You would seen procedures in Oracle that will call a subroutine within the same procedures for 50 times in different lines in the procedure. Now how do you recreate the same procedure in SQL Server?

Explanation below:

In SQL Server procedure, If subroutine is performing data modifications or need to use temporary tables(#teporary_table) it needs to be stored procedure. In other cases, you can use create a function. You can choose from different types of functions depending on their complexity and return values:

Simple Stored procedure will look like

DECLARE @Input INT
DECLARE @Result INT

//Some other SQL statements here...

EXEC myStoredProcedure @Input, @Result OUT

1. Scalar function – returns one value
2. Inline table-valued – it’s like view with parameters (you cannot declare table variables for example, its one SELECT statement) and returns rowset
3. Multi-statement table-valued – returns rowset but you can have more complex logic

In SQL Server there is SQL CLR and you can create functions or procedures (or as called them “subroutines”) in .net code also.

In SQL Server, we have stored procedures and table and scalar functions. Another feature that is convenient are CTE (common table expressions). Please refer for article here for complete CTE details https://sqlserver-dba.co.uk/cte/sql-server-tsql-cte-common-table-expressions.html

For example:

Common table expressions are very nice for readability where resultset from CTE is needed multiple times:

;WITH CTPName AS
(
// complex SQL statements
)
SELECT *
FROM myTable INNER JOIN CTPName ON...

Functions you can use directly in SQL statements (SELECT, INSERT, UPDATE, JOIN), so they seamless to reuse.

Advantages and Disadvantages of Using SQL Stored Procedures

A Stored Procedure is a type of SQL code with queries used in SQL that can be stored for later use and can be used as many times needed.

Whenever we need to execute the query/set of queries, instead of calling it/them individually,  you can just call the stored procedure with queries in it. You can also pass parameters to a stored procedure, so that the stored procedure can take input parameters values that are passed as input.

Syntax :

CREATE PROCEDURE procedurename
AS
sql_statements
GO;

To execute a stored procedure –

EXEC procedurename;

Simple Stored procedure Example :

CREATE PROCEDURE Address
AS
SELECT *
FROM homeaddress
GO;

The main advantages of stored procedure are given below:

Better Performance –

The procedure calls are quick and efficient as stored procedures are compiled once and stored in executable form. Hence the response time is quick. The executable code is automatically cached, hence lowers the memory requirements in SQL Server.

Higher Productivity ––

Since the code/ queries are used again and again so, it results in higher productivity.

Ease of Use ––

To create a stored procedure, once you get hang of it, it will be a life saver for repeated queries and parameter inputs can be used for various different outputs. For SSRS reports also, stored procedures make things really easy with parameters.

Scalability ––

Stored procedures in SQL Server increase scalability by isolating application processing on the server.

Maintainability ––

Maintaining a procedure on a SQL server is much easier then maintaining copies on various client machines, this is because scripts are in one location.

Security –
Access to the data can be restricted by allowing users to manipulate the data only through stored procedures that execute with their login’s privileges.

The main disadvantages of stored procedures are given below:

Testing ––

Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime. But once you get hang of it, it is fairly simple.

Debugging ––

Depending on the database technology, debugging stored procedures will either be very difficult. SQL Server have some debugging capabilities.

Versioning ––

Version control is not supported by the stored procedure. But manually can be implemented using commenting and making backup of stored procedures before making any changes.

Cost ––

An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost but will be good money spent.

Portability ––

MIgration of one Database server to another like,from one database type(Oracle) to another database type(MS SQL Server) will need Stored procedures rewriting.

For Additional questions and more details on below if not clear above, please comment………

Some of these are already covered in my previous articles…

1. sql server sub procedure
2. stored procedure vs function in sql server performance
3. sql stored procedure
4. difference between stored procedure and function in sql server
5. difference between function and stored procedure and trigger in sql server
6. difference between stored procedure and function and view in sql server
7. difference between stored procedure and function in oracle
8. stored procedure subroutine in sql
9. Advanced stored procedure examples in SQL Server