Parse SQL in Stored Procedures

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I need to parse SQL in Sybase stored procedures so I can create other stored procedures based of the original. I have looked at SQL::Statement, SQL::Translator etc but these don't seem to be able to do what I want.
The kind of thing I want to do is:

    Original Sproc
    
    CREATE PROC testProc
    as
        SELECT stuff FROM myTable
        WHERE which_stuff = "my stuff"
        go
        
        New Proc (myTable has a historic equivalent)
        
        CREATE PROC testProc_hist
        (
            @hist_date    datetime
        )
    as
        SELECT stuff FROM myTable_hist
        WHERE  which_stuff = "my stuff"
        AND    hist_date = @hist_date
        go
[download]

or to add missing where clauses
e.g. all queries should check if row has not been deleted

        Original Sproc
        
        CREATE PROC testProc
    as
        SELECT stuff FROM myTable
        WHERE  which_stuff = "my stuff"
        go

        Modified Sproc

        CREATE PROC testProc
    as
        SELECT stuff FROM myTable
        WHERE  which_stuff = "my stuff"
        AND    is_deleted = 'N'
        go
[download]

Thanks !

Comment on Parse SQL in Stored Procedures Select or Download Code

Replies are listed 'Best First'.
Re: Parse SQL in Stored Procedures by whereiskurt (Friar) on Jul 05, 2007 at 19:41 UTC
Your post is pretty hard to understand. I think you're trying build some sort of dynamic SQL query that creates stored procedures...? Hmm... It seems like you want to be able to modify 'testProc_hist' after it's been created? My suggestions is to use a template engine like HTML::Template to create your stored procedure template. When you need to change a stored procedure, find replace TOKEN values in the template (e.g. $SELECT_CLASE, $WHERE_CLAUSE, etc.) and then execute it as an 'ALTER PROCEDURE' statement. The idea of 'parsing SQL in Sybase stored procedure' is WAY WAY to complicated - instead do your processing outside of the stored procedure and then execute an ALTER PROCEDURE statement instead. Good luck? Kurt	[reply]

Replies are listed 'Best First'.

Re: Parse SQL in Stored Procedures
by whereiskurt (Friar) on Jul 05, 2007 at 19:41 UTC

Your post is pretty hard to understand. I think you're trying build some sort of dynamic SQL query that creates stored procedures...? Hmm... It seems like you want to be able to modify 'testProc_hist' after it's been created?

My suggestions is to use a template engine like HTML::Template to create your stored procedure template. When you need to change a stored procedure, find replace TOKEN values in the template (e.g. $SELECT_CLASE, $WHERE_CLAUSE, etc.) and then execute it as an 'ALTER PROCEDURE' statement.

The idea of 'parsing SQL in Sybase stored procedure' is WAY WAY to complicated - instead do your processing outside of the stored procedure and then execute an ALTER PROCEDURE statement instead.

Good luck?

Kurt

[reply]