Calling a T-SQL script from within a T-SQL script

Question

Calling a T-SQL script from within a T-SQL script

Lee Morrison

SSC Enthusiast

Points: 193
More actions
March 14, 2003 at 3:10 pm

#80472

Is there any way to call a T-SQL script from within a T-SQL script?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

You can use xp_cmdshell to run isql with the script. Don't really see why you would need that though, are you sure that is the right way of doing what you need?

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Lee Morrison SSC Enthusiast Points: 193 More actions · Answer 2

quote:
You can use xp_cmdshell to run isql with the script. Don't really see why you would need that though, are you sure that is the right way of doing what you need?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu

Lee Morrison SSC Enthusiast Points: 193 More actions · Answer 3

Thank's for the reply and I am familiar with calling the command procedure running the script in batch.

Perhaps there is a better way? We have templates defined to create a database and then scripts to create tables, procedures, views, triggers, etc. I am trying to create a script that would call each of these "sub scripts" rather than combine them all into one big script.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

To my knowledge there is no way to do this in T-SQL. I think Oracle has some syntax for it, that may be why some want this. I can't see any reason to include it in T-SQL. If anything, there could be some way to set it up in Query Analyzer 'automatically'. What you can do is create a command-line batch script that uses osql to run each script.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

tcartwright SSCertifiable Points: 5573 More actions · Answer 5

I would actually suggest using VBScript or JScript via WSH to execute your

scripts. I used to do something similar to what you are talking about with batch

files, but the scripting languages provide so much more capabilities I rarely go

back to dos batch mode. Also with WSH 5.6 there is remote scripting, so that you

can run scripts remotely on other computers. I have written a script that lets

me remotely re-register my com plus objects on my middle tier server. I copy

them over then kick this script off remotely. There are other options : VNC,

Term Serve, Telnet, SSH, etc...., but I like this one as it does not even

require me to have the scripts I am running on the target server. Lets me do

some very inventive administrative work from my desk. And as I am lazy, that is

a good thing. I work really hard so I can take it easy...

Tim C.

//Will write code for food

Tim C //Will code for food

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

That sounds like a great solution, and yes, WSH is probably better than batch script. Just me that's oldfashioned. 🙂

But still, neither of these solutions makes it possible to call a T-SQL script from within a T-SQL script. But as I said, I don't really see any reason to do that.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

tcartwright SSCertifiable Points: 5573 More actions · Answer 7

Your right, without using xp_cmdshell I don't see another way either. I was trying to offer him an alternative. I really did not understand the need to have it run from TSQL to begin with either. He could use WSH to kick off i/osql, and capture the StdOut from it running.

Tim C.

//Will write code for food

Tim C //Will code for food

eugene_liu SSC Journeyman Points: 87 More actions · Answer 8

Yes, You can. Make your scripts into store procedure then be called by your command T-SQL script. If this would work for you ?

tcartwright SSCertifiable Points: 5573 More actions · Answer 9

eugene I think he was referring to being able to call .sql file(s) from within T-SQL file if I am not mistaken. Which is better left to batch files or script files.

Tim C.

//Will write code for food

Tim C //Will code for food