Search code examples
sql-servert-sqlstored-procedures

Subroutine in a SQL Server stored procedure

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?

Solution

  • 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.