How to call a sql script from another in SQL Server 2005

Question

Hello to all,

I am in need of testing several different processes for the application we're builduing. Each process requires a particular table in our database to have data and all of these tables have foreign key constraints from other tables as well.

I've written sql scripts that populate the table I'm interested in as well as its dependencies but, it turns out that in a few of these scripts I've duplicated a lot of code when populating the dependencies tables.

I would like to take out the duplicated code and put it in a separate script but I don't know how, if possible, to execute a sql script from within another one.

An important part of all of this would also be to be able to get the @@IDENTITY value in the calling script from the called one.

Any help will be greately appreciated.

Best regards.

Clarification: By script I mean a file saved in disk. I don't want to be creating and deleting temporary stored procedures for this.

Answer 1

When I hear the word "script", I think of a file containing a series of commands; if you're asking how to get SQL Server to load a file of commands from another file of commands, I'm not sure of an easy way to do that.

If you can save your duplicate code as a stored procedure, you can certainly call a stored procedure from another stored procedure within SQL Server. You could then pass in a parameter holding the @@IDENTITY value (and you may want to look at SCOPE_IDENTITY() instead).

HTH, Stu