Chain transact sql scripts

I have two Transact SQL scripts and I want to call the second script from the first –
is this possible in SQL server 2005?
I am trying to do a port from Oracle (where this is possible) but cannot find the mechanism to do so in SQL Server.

Eg if Script1.sql is
BEGIN
PRINT (‘Inside Script1’)
//Invoke Script2.sql – how do I do this?
END

where Script2.sql is
BEGIN
PRINT (‘Inside Script2’)
END

When I execute Script1.sql - I need it to print both 'Inside Script2' and 'Inside Script1'

You can do this using the :r command if you invoke the script using the new SQLCMD command-line utility. It also has other script pre-processing features. You can take a look at the Books Online topic at link below:

http://msdn2.microsoft.com/en-us/library/ms162773(SQL.90).aspx

|||We can use the :r option inside sqlcmd - but was looking for Transact SQL support (without using xp_cmdshell to spawn "sqlcmd" inside a larger script) - Inside a Oracle pl/sql script, we use "@.<scriptname> from a enclosing script" - was looking for similar functionality in Transact SQL|||

I would suggest the best way would be to place the code inside two stored procedures, and call one from the other.

HTH

|||

shuges is right

store procedure can call another sp

using the exec "spname" syntax

also sql queries can be nested so it is possible to

rewrite two sps into one.

moreover you can also make use of functions

|||The :r SQLCMD command is the equivalent of @. command in SQLPLUS. These are client-side features not part of the TSQL or PL/SQL language.