oracle "@script" equivalent 1

[misterstick]

i'm moving from oracle to sql server.

in oracle i'm used to using modular scripts, including the text of one script in another.

e.g.

<script name=script1>
  create table xxtest as ( field1 char(10) not null );
</script>

<script name=script2>
  @script1
</script>

running script2 would then run script1 and create the table.

i can't see how to do this in sql server.

any ideas?


mr s. <

 

[bborissov]

DECLARE @sql varchar(8000)
SET @sql = 'EXEC (''create table xxtest as ( field1 char(10) not null )'')'

EXEC (@sql)

NOT TESTED!



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[misterstick]

ok, so that allows me to execute arbitrary code, for which many thanks.

what if i want to keep the code in a script file on disk?
how do i then call that code from within a different script?

in oracle, it's @script or @@script (although @@ is borked by design)
in C it's #include <script.h>
in Perl it's use script.pm
in PHP, it's include() or require()
in Powerhouse it's use script nolist nodetail

any ideas?

mr s. <

 

[bborissov]

If you use SQL Server 2005 or later you could use .NET CLR integration and read that file and then execute that script.
Also you could save all as pure T-SQL code (SQL Server native language) in some text file and than use sqlcmd command-line utility to execute that file. Just check SQLCMD in BOL.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Check this thread

http://forums.asp.net/p/1379288/2911157.aspx#2911157

for ideas of how to do this from .NET application

 

[misterstick]

ok, so the answer is "you can't."

that's so alien.

all my programming career i've believed that each unit of code should be kept separate. SSMS allows you to do this with its creation of container documents or "solutions".

that t-sql then gives you no way to automate the running of those scripts natively, and forces you to go to a different scripting language is strange and frightening to me.

yay! learning curve.

many thanks,

mr s. <

 

[misterstick]

or rather, the answer is "use sqlcmd"

the command is :r script.

how hard was that?

mr s. <