Can we write a sub function or procedure inside another stored procedure - SQL Server

Question

Hi,

I want to check if SQL Server(2000/05/08) has the ability to write a nested stored procedure, what I meant is - WRITING a Sub Function/procedure inside another stored procedure. NOT calling another SP.

Why I was thinking about it is- One of my SP is having a repeated lines of code and that is specific to only this SP.So if we have this nested SP feature then I can declare another sub/local procedure inside my main SP and put all the repeating lines in that. and I can call that local sp in my main SP. I remember such feature is available in Oracle SPs.

If SQL server is also having this feature, can someone please explain some more details about it or provide a link where I can find documentation.

Thanks in advance Sai

Answer 1

It does not have that feature. It is hard to see what real benefit such a feature would provide, apart from stopping the code in the nested SPROC from being called from elsewhere.

Answer 2

I don't recommend doing this as each time it is created a new execution plan must be calculated, but YES, it definitely can be done (Everything is possible, but not always recommended).

Here is an example:

CREATE PROC [dbo].[sp_helloworld]
AS
BEGIN
    SELECT 'Hello World'
    DECLARE @sSQL VARCHAR(1000)
    SET @sSQL = 'CREATE PROC [dbo].[sp_helloworld2]
      AS
      BEGIN
       SELECT ''Hello World 2''
      END'
    EXEC (@sSQL)

    EXEC [sp_helloworld2];
    DROP PROC [sp_helloworld2];
END

You will get the warning

The module 'sp_helloworld' depends on the missing object 'sp_helloworld2'.
The module will still be created; however, it cannot run successfully until
the object exists.

You can bypass this warning by using EXEC('sp_helloworld2') above.

But if you call EXEC [sp_helloworld] you will get the results

Hello World
Hello World 2

Answer 3

I agree with andynormancx that there doesn't seem to be much point in doing this.

If you really want the shared code to be contained inside the SP then you could probably cobble something together with GOTO or dynamic SQL, but doing it properly with a separate SP or UDF would be better in almost every way.

Answer 4

John's sp_helloworld does work, but here's the reason why you don't see this done more often.

There is a very large performance impact when a stored procedure is compiled. There's a Microsoft article on troubleshooting performance problems caused by a large number of recompiles, because this really slows your system down quite a bit:

http://support.microsoft.com/kb/243586

Instead of creating the stored procedure, you're better off just creating a string variable with the T-SQL you want to call, and then repeatedly executing that string variable.

Don't get me wrong - that's a pretty bad performance idea too, but it's better than creating stored procedures on the fly. If you can persist this code in a permanent stored procedure or function and eliminate the recompile delays, SQL Server can build a single execution plan for the code once and then reuse that plan very quickly.

Answer 5

Thank you all for your replies! I'm better off then creating one more SP with the repeating code and call that, which is the best way interms of performance and look wise.

Answer 6

Oracle's PL/SQL is something of a special case, being a language heavily based on Ada, rather than simple DML with some procedural constructs bolted on. Whether or not you think this is a good idea probably depends on your appetite for procedural code in your DBMS and your liking for learning complex new languages.

The idea of a subroutine, to reduce duplication or otherwise, is largely foreign to other database platforms in my experience (Oracle, MS SQL, Sybase, MySQL, SQLite in the main).

While the SQL-building proc would work, I think John's right in suggesting that you don't use his otherwise-correct answer!

You don't say what form your repeated lines take, so I'll offer three potential alternatives, starting with the simplest:

1. Do nothing. Accept that procedural SQL is a primitive language lacking so many "essential" constructs that you wouldn't use it at all if it wasn't the only option.

2. Move your procedural operations outside of the DBMS and execute them in code written in a more sophisticated language. Consider ways in which your architecture could be adjusted to extract business logic from your data storage platform (hey, why not redesign the whole thing!)

3. If the repetition is happening in DML, SELECTs in particular, consider introducing views to slim down the queries.

4. Write code to generate, as part of your build process, the stored procedures. That way if the repeated lines ever need to change, you can change them in one place and automatically generate the repetition.

That's four. I thought of another one as I was typing; consider it a bonus.