Subroutine in a SQL Server stored procedure

Question

Can anyone share me a light on how to create a subroutine in a stored procedure in SQL Server?

I have a procedures in Oracle that will call a subroutine within the same procedures for 50 times in different lines in the procedure. Now I want to recreate the same procedure in SQL Server. Could you share me some light on how to do this?

Theatre

There isn't such a thing as a subroutine in SQL Server. But a stored procedure can call another stored procedure. – Bookmark

Answer 1

If your subroutine is performing data modifications (CRUD) 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:

• scalar function - returns one value
• inline table-valued - it's like view with parameters (you cannot declare table variables for example, its one SELECT statement) and returns rowset
• multi-statement table-valued - returns rowset but you can have more complex logic

Also, in SQL Server there is SQL CLR and you can create functions or procedures (or as called them "subroutines") in .net code.

Anaptyxis

Answer 2

In SQL Server you have stored procedures and table and scalar functions. Another feature that is convenient are common table expressions.

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

Table function:

SELECT *
FROM myFunction(@UserID) as f
    INNER JOIN users AS u ON f.UserID = u.ID

Scalar function you can put in SELECT part of query.

SELECT u.ID, myFunction(u.ID)
FROM users as u

Common table expressions are nice for readability:

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

You can have multiple CTEs before SELECT statement, but you can only use them in the statment above which they were declared.

Stored procedures

They are a bit of pain in the a$$, because you need to capture result into variables in order to use them in the calling stored procedure.

 DECLARE @Input INT
 DECLARE @Result INT

 EXEC myStoredProcedure @Input, @Result OUT

If procedure outputs table, it gets even more complicated. You need to use cursor to capture result.

Incantatory

Answer 3

A trick:

create proc MyProc @MySub varchar(20) = ''
as
if @MySub = 'MySub' goto MySub

print 'MyProc'
exec MyProc MySub
return

MySub:
print 'MySub'
return

Momus

What is the problem with GOTO?. Not only is this bad code in any language and doesn't really answer the question, there are good answers already – Marche