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

Question

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.

I've never had a need for it, but I think you could do this with the osql utility; however, I don't know how you could get the appropriate IDENTITY value passed from one to the other. You may have to build and delete either a holding table or some sort of cookie (which would mean using the xp_cmdshell stored proc). — Stuart Ainsworth
– Stuart Ainsworth, Commented Oct 8, 2009 at 19:58

Stuart Ainsworth · Accepted Answer · 2009-10-05 18:29:23Z

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

answered Oct 5, 2009 at 18:29

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related