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.

CAST error with nvarchar to int (Error converting data type nvarchar to int)

I am receiving an error with a transact query when performing a CAST from an nvarchar to int. For example:

SELECT myField1, CAST(myField2 as int) FROM tbl_myTable

MSDN article describe that this casting error "Error converting data type nvarchar to float" is caused by an invalid non-numeric entry.

Is there a way to perform an in-line test to perform a check to determine a course of action? For example:

SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Are above expression, or something similar to it, even possible in transact-SQL?

Thank you,
Each column in a select stmt can only be one data type. Why do you need to check each row to see if it is an int or not?|||Due to requirements, it is necessary to "transform" data from nvarchar into int (in a massive sweep).

I think I have been able to figure this out using CAST

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASECAST isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||

CAST and CONVERT are the same, CAST is just the standard.

Code Snippet

SELECT field1, CASE WHEN IsNumeric(myField2) = 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM table

anything other than NULL and int wont work though.|||Sorry for a bit of a typo, meant to write the following:

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASE isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||yes, that works fine|||Thank you to everyone who replied!