Call .sql from .sql

[bmahf]

Hi all,

I am a developer new to working with SQL Server. I am on 2008, and have created n number of stored procedures. Each stored procedure has been put in its own file on my system, and stored in source control. This is so that, as I continue to update the system, I can just grab the one stored procedure I am working on updating, run it to update it in the database, and then commit my changes.

The problem is that when the DBA makes changes to the database and I run his script to drop and recreate mine accordingly, I lose all of my stored procedures. I have looked online for a way in MS SQL Server to just have one master .sql file that I can call that will call all of the others and recreate all my procedures in the database. My search has been fruitless so far. It almost seems like there is no way to do this. Am I correct? Please someone tell me I've missed something, and then it would be great if you could fill me in on what it is I've missed.

Thanks for your help

[Rabbit]

What script are you running? And why would it drop everything just because a change was made to the database?

[NeoPa]

What's the SQL you use to drop an individual SP? It sounds like this may be dropping all of yours. We have nothing to work with of course as you included nothing in the way of SQL to illustrate the situation :-(

[bmahf]

So I didn't add sql code because the question was how to call one sql file from another, not how to fix a problem I was having with sql code. As to the database being dropped and recreated, it's my local dev system, as opposed to the actual test db being handled by the dba, so I just run the create script since I don't want to have to deal with the specifics up moving from one db schema to another.

As to dropping my sp's, that's not what's happening. The new database instance is being created by dropping the old, therefore dropping everything in the old instance.

Anyway, I continued to look and settled for doing a .bat file using the sqlcmd tool. My file does a sqlcmd call for each file that I want run. Pretty nice, but I was really hoping not have to get out of SSMS to go to a command window and run a bat file, but one does what one has to do.

Thanks for the help...

[NeoPa]

It's a shame your first post wasn't quite so clear on what you didn't want. That's cleared that up anyway.

bmahf:
So I didn't add sql code because the question was how to call one sql file from another

Now all we need to know is what you mean when you refer to SQL files. The only command I know of to invoke one set of SQL from another in T-SQL is the EXEC[UTE]() procedure, but that works with entities within your database. I know of nothing that refers to external files.