Can we write a sub function or procedure inside an

问题:

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

回答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.

回答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

回答3:

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.

回答4:

CREATE TABLE #t1 (digit INT, name NVARCHAR(10));  
GO

CREATE PROCEDURE #insert_to_t1  
(  
    @digit INT  
,    @name NVARCHAR(10)  
)  
AS  
BEGIN  
    merge #t1 AS tgt  
    using (SELECT @digit, @name) AS src (digit,name)  
    ON    (tgt.digit = src.digit)  
    WHEN matched THEN  
          UPDATE SET name = src.name  
    WHEN NOT matched THEN  
          INSERT (digit,name) VALUES (src.digit,src.name);  
END;  
GO  


EXEC #insert_to_t1 1,'One';  
EXEC #insert_to_t1 2,'Two';  
EXEC #insert_to_t1 3,'Three';  
EXEC #insert_to_t1 4,'Not Four';  
EXEC #insert_to_t1 4,'Four'; --update previous record!  


SELECT    * FROM #t1;

What we're doing here is creating a procedure that lives for the life of the connection and which is then later used to insert some data into a table.

回答5:

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.

回答6:

I just had a similar situation in a SQL Trigger (similar to SQL procedure) where I basically had same insert statement to be executed maximum 13 times for 13 possible key values that resulted of 1 event. I used a counter, looped it 13 times using DO WHILE and used CASE for each of the key values processing, while kept a flag to figure out when I need to insert and when to skip.

回答7:

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.

回答8:

I too had need of this. I had two functions that brought back case counts to a stored procedure, which was pulling a list of all users, and their case counts.

Along the lines of

select name, userID, fnCaseCount(userID), fnRefCount(UserID)
  from table1 t1
  left join table2 t2
    on t1.userID = t2.UserID

For a relatively tiny set (400 users), it was calling each of the two functions one time. In total, that's 800 calls out from the stored procedure. Not pretty, but one wouldn't expect a sql server to have a problem with that few calls.

This was taking over 4 minutes to complete.

Individually, the function call was nearly instantaneous. Even 800 near instantaneous calls should be nearly instantaneous.

All indexes were in place, and SSMS suggested no new indexes when the execution plan was analyzed for both the stored procedure and the functions.

I copied the code from the function, and put it into the SQL query in the stored procedure. But it appears the transition between sp and function is what ate up the time.

Execution time is still too high at 18 seconds, but allows the query to complete within our 30 second time out window.

If I could have had a sub procedure it would have made the code prettier, but still may have added overhead.

I may next try to move the same functionality into a view I can use in a join.

select t1.UserID, t2.name, v1.CaseCount, v2.RefCount
  from table1 t1
  left join table2 t2
    on t1.userID = t2.UserID
  left join vwCaseCount v1
    on v1.UserID = t1.UserID
  left join vwRefCount v2
    on v2.UserID = t1.UserID

Okay, I just created views from the functions, so my execution time went from over 4 minutes, to 18 seconds, to 8 seconds. I'll keep playing with it.

回答9:

it would be very nice if MS develops GOSUB besides GOTO, an easy thing to do!

Creating procedures or functions for "internal routines" polute objects structure.

I "implement" it like this

BODY1:

goto HEADER HEADER_RET1:

insert into body ...

goto BODY1_RET

BODY2:

goto HEADER HEADER_RET2:

INSERT INTO body....

goto BODY2_RET

HEADER:

insert into header

if @fork=1 goto HEADER_RET1

if @fork=2 goto HEADER_RET2

select 1/0 --flow check!

回答10:

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.