Home » difference between stored procedure and function in sql server

difference between stored procedure and function in sql server

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

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, Read the rest