Build Tool for SQL Script

[PorkPie]

I am trying to refactor a load of spaghetti code in some SQL I've inherited. Currently it's in one massive script with loads of temp tables and and interdependencies. I want to split this out into manageable files to start to understand the dependencies and make the source code easier to manage.

I would like to be able, for example, to define a table in one file, and then import it in another that requires the table to be present to compile. E.g.

-- File 1 table defs -------------------------------
-- table definition
TABLE_1 [
create table T (id int, name varchar(20))
go
]

-- End File 1 -------------------------------------

-- File 2 -- sp_FirstProc

[INSERT TABLE_1]

create procedure sp_FirstProc
@myId int
as
begin

select * from T where id=@myId

end

---- end file -------------------------

when I run the build script it replaces the tokens in the second file with the one defined in the first to produce:

--- file created by build script--------

create table T (id int, name varchar(20))
go

create procedure sp_FirstProc
@myId int
as
begin

select * from T where id=@myId

end

--- end file created by build script--------

Any suggestions? Thanks!

[TheFaQQer]

Script generate.sql:

def table_name = &1

create table &table_name ( col1 varchar2(100), col2 number)
/

create or replace procedure MYPROC&table_name AS
  v1 varchar2(100);
begin
  select col1 into v1 from &table_name where rownum = 1;
end;
/

then @generate <table name>

works in Oracle - dunno if other databases allow the same flexibility, though.

[mudskipper]

I am trying to refactor a load of spaghetti code in some SQL I've inherited. Currently it's in one massive script with loads of temp tables and and interdependencies. I want to split this out into manageable files to start to understand the dependencies and make the source code easier to manage.

I would like to be able, for example, to define a table in one file, and then import it in another that requires the table to be present to compile. E.g.

-- File 1 table defs -------------------------------
-- table definition
TABLE_1 [
create table T (id int, name varchar(20))
go
]

-- End File 1 -------------------------------------

-- File 2 -- sp_FirstProc

[INSERT TABLE_1]

create procedure sp_FirstProc
@myId int
as
begin

select * from T where id=@myId

end

---- end file -------------------------

when I run the build script it replaces the tokens in the second file with the one defined in the first to produce:

--- file created by build script--------

create table T (id int, name varchar(20))
go

create procedure sp_FirstProc
@myId int
as
begin

select * from T where id=@myId

end

--- end file created by build script--------

Any suggestions? Thanks!

Can't think how you'd actually include another script, but you could stick it in a sp and call that.

If you're trying to achieve a variable table name, you'll probably need to use dynamic SQL

It might be worth checking out common table expressions - they can be useful for simplifying SQL - especially if you have something like a similar subselect being called multiple times.

[Jaws]

Assuming you are using SQL server:

You can run scripts built in that way using sqlcmd.exe. For instance you can include variables and import files.

For example you can use :r <path> in the script to include a file.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

I'd guess SQLCMD does not generate an output SQL script - but it could be an option if you can use it to deploy to the DB.

[PorkPie]

Thanks all,

Perhaps I should make this more generic. All I want to do is include stuff from one file with stuff from a build file to generate a merged output.

---------file 1---------

stuff to include

blah blah blah

-----------------------

-------- file 2 skeleton ---------

<include file 1>

do stuff in file 2

foo bar foo bar

---------- end of file -----------

>> Run build

------------ resulting file --------

stuff to include

blah blah blah

do stuff in file 2

foo bar foo bar

----------end resulting file -------------

does that make sense?

[Jaws]

Thanks all,

Perhaps I should make this more generic. All I want to do is include stuff from one file with stuff from a build file to generate a merged output.

---------file 1---------

stuff to include

blah blah blah

-----------------------

-------- file 2 skeleton ---------

<include file 1>

do stuff in file 2

foo bar foo bar

---------- end of file -----------

>> Run build

------------ resulting file --------

stuff to include

blah blah blah

do stuff in file 2

foo bar foo bar

----------end resulting file -------------

does that make sense?

Do you have visual studio installed? There is a command line utility for transforming T4 templates (which support includes) into files. I believe it's available from the VS command line.

The utility is called "TextTransform.exe" and you'd include <#@ include file="table.sql" #> in your files.

I've used it with great success to generate a heap of SQL code in the past.

[PorkPie]

Do you have visual studio installed? There is a command line utility for transforming T4 templates (which support includes) into files. I believe it's available from the VS command line.

The utility is called "TextTransform.exe" and you'd include <#@ include file="table.sql" #> in your files.

I've used it with great success to generate a heap of SQL code in the past.

Cool thanks - will take a look

[Spacecadet]

I would like to be able, for example, to define a table in one file, and then import it in another that requires the table to be present to compile.

Are you using SQL Server?
Tables don't need to exist for a procedure to compile:

create procedure deferred_test
as
begin
select * from dofkdofkdokf
end

should compile fine