CODEWITHSUNDEEP
sql-servert-sql
user1459519
user1459519

Reputation: 720

Invoking a *File* of T-SQL commands from within a T-SQL script

I need to "inject" a file that contains a T-SQL procedure into many T-SQL scripts. Something along the lines of :

EXECUTE  '../CSV_From_Sql.sql', #CsvFileCreator <other parameters>

where #CsvFileCreator is the entry point (i.e. the procedure name) in '../CSV_From_Sql.sql'.

Note that results produced by #CsvFileCreator must be within (accessable from) the scope/namespace of the invoking script.

At the moment I am not allowed to create stored procedures. To meet the scoping requirements #CsvFileCreator must be "injected" (copied) into each script. This has to be done in a way that allows #CsvFileCreator to be simultaneously updated in all queries (and eventually converted to a stored procedure).

The desired effect is, in essence, an "insert text here" operation (i.e. replace the EXECUTE statement with the contents of the file). Which should be extremely simple to do except that Microsoft's documentation does not seem to allow for this.

Some context: In my case SQL server is being used as the back end to a python 3.X GUI report generator. End users have absolutely no access to the SQL code and there's no opportunity for injection. All users can do is make their choices (via checkboxes, spinboxes, etc) and press a "Create Reports" button (this causes python to invoke SQL server). Also the very nature of the app means it will NEVER be on a network that is connected to the outside world.

For reasons that I will not get into, only "pure" T-Sql commands can be used and the use of any type of manual operation (e.g. use of tools like SSMS, bcp, sqlcmd, etc.) is not possible. In my case I could automatically insert this "boilerplate procedure" before invoking each query.

Is there a pure T-SQL way to get the "execute file" or "copy file" effect?

Upvotes: 0

Views: 61

Answers (1)

Ann L.
Ann L.

Reputation: 13965

To the best of my knowledge, it isn't possible to open a text file from within a stored proc and execute its contents. And, as has been pointed out in the comments, this has BIG ramifications for security.

Having said that, it is possible to execute dynamic SQL, using sp_execute_sql. (See here for full details.) And where you get the chunk of text that you execute is up to you.

So, some possibilities include:

  • Pass text to script as a parameter
  • Return text to script as the result of a scalar function. (Never done this, but ought to be possible.)
  • Read text to execute from a table

You refer to "scripts", and you also refer to them as "queries". So I am assuming these are executed through various means, and that you don't have one central application routine that executes them (and could be modified to do other things before hand.)

A final thought. T-SQL is not a language/environment in which file IO or text processing can be easily done, and it doesn't allow any introspection. But, Windows (or other operating systems) are great for that.

If these scripts are living as "scripts" -- text files -- you could write a program in Windows to pre-process them and output a file that includes the merged SQL, which you then execute. Exactly what you wanted, except that it happens in Windows rather than in T-SQL.

Upvotes: 1

Related Questions