Writing Recursive StoredProcedures

Question

Basically what i want in my stored procedure is to return a list of tables, store this list in a variable; i need to go through every item in my list to recursively call this storedprocedure. In the end i need an overall listOfTables built up of this recursion. Any help would be most appreciated

Answer 1

You should take a look at Common Table Expressions in case you're on SQL2005 or higher (not sure if they can help in your specific situation but an important alternative to most recursive queries) . Recursive procedures cannot nest more than 32 levels deep and are not very elegant.

Answer 2

You can use CTE's:

WITH  q (column1, column2) (
        SELECT  *
        FROM    table
        UNION ALL
        SELECT  *
        FROM    table
        JOIN    q
        ON      …
        )
SELECT   *
FROM     q

However, there are different limitations: you cannot use aggregates, analytics functions, TOP clause etc.

Answer 3

Are you after recursion or just a loop through all tables? If you are using Sql Server 2005 and want to loop through all tables you can use a table variable in your SP, try something along thse lines:

declare @TableList as table (
    ID int identity (1,1),
    TableName varchar(500)
)

insert into @TableList (TableName)
select name
from sys.tables

declare @count int
declare @limit int
declare @TableName varchar(500)

set @count = 1
select @limit = max(ID) from @TableList

while @count <= @limit
    begin
     select @TableName = TableName from @TableList where ID = @count
     print @TableName --replace with call to SP

     set @count = @count + 1
    end

Replace the print @TableName with the call to the SP, and if you don't want this to run on every table in the DB then change the query select name from sys.tables to only return the tables you are after

Answer 4

Most likely a CTE would answer your requirement.

If you really must use a stored procedure not a query then all you have to do is iterate through the table list then you can use your code of choice to iterate through the table list and call the procedure. And Macros already posted how to do that as I was typing lol. And as Mehrdad already told you, there is limit on the number of nested levels of call SQL Server allows and is rather shallow. I'm not convinced from your explanation that you need a recursive call, it looks more like a simple iteration over a list, but if you do indeed need recursivity then remember CS 101 class: any recursive algorithm can be transformed into a non-recursive one by using a loop iteration and a stack.

Answer 5

Stored procedures are very useful. BUT.

I recently had to work on a system that was heavily dependent on stored procedures. It was a nightmare. Half the business logic was in one language (Java, in this case), and the other half was in the database in stored procedures. Worse yet, half the application was under source code control and the other half was one database crash from being lost forever (bad backup processes). Plus, all those lovely little tools I have for scanning, analyzing and maintaining source code can't work with sources inside the database.

I'm not inherently anti-stored-procedure, but oh, how they can be abused. Stored procedures are excellent for when you need to enforce rules against data coming from a multiplicity of sources, and there's no better way to offload heavy-duty record access off the webservers (and onto the DBMS server). But for the most part, I'd rather use a View than a Stored Procedure and an application programming language for the business logic. I know it makes some things a little more complex. But it can make life a whole lot easier.