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?
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