TSQL call query from wtihin a query in same database

Question

I am blaming this on Monday morning, but is there some way to run a query from within a query.....

Isn't there some sort of command like

EXEC sp_ myOtherQuery.sql

I realize this lacks all sorts of error handling just wondering if there was some way to do this.

Its not an SP though, the SP_ was a call to the default procedures that might be able to call the query to run like sp_rename when renaming tables. — htm11h, Mar 23 '15 at 16:09
Ah, sorry. My eyes missed the space. SQL generally doesn't like working with files directly. — TZHX, Mar 23 '15 at 16:11
SQL as a product does not really interact with .sql files. That is just how SSMS saves the files. — paparazzo, Mar 23 '15 at 16:23
Is it possible? Yes. Is it easy to do or recommended? No. http://stackoverflow.com/questions/241925/is-it-possible-to-execute-a-text-file-from-sql-query — Stephan, Mar 23 '15 at 16:41
Why is it not recommended? This is not a text file, it is native SQL to the same DB. No parameters will be passed. Just some processing and table copies created. — htm11h, Mar 23 '15 at 17:11

score 0 · Accepted Answer · answered Mar 23 '15 at 16:19

0

I had similar situation and used something like:

declare @cmd nvarchar(max)
declare @spname nvarchar(256)
set @spname=<your_database_name> + '.dbo.' + 'sp_executesql'
set @cmd='your query text'
execute @spname @cmd

answered Mar 23 '15 at 16:19

Shkutu

51
4

So I can only call a SP? Well okay I think I see this let me give it a try. – htm11h Mar 23 '15 at 16:21

score 0 · Answer 2 · edited Mar 23 '15 at 17:38

If you're doing this in SQL Server Management Studio:

Turn on SQLCMD mode using the query menu. Then use the :r directive to include the other sql file into the one you're editing.

--sql statements
:r "C:\Full\Path\To\Sql\File.sql"
--More sql statements

This is effectively an include directive. The contents of the sql file will be inserted in your script at run time and the whole thing will be run as one.

This works only in SSMS. It isn't something you can do in a stored procedure.

TSQL call query from wtihin a query in same database

2 Answers2