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

Question

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?

So you need to pass the (string?) contents of a file as a parameter to multiple sprocs? Also, I wouldn't say it should be extremely simple considering that your limitations include using none of the tools that could possibly help... no powershell either I assume? — Jacob H
– Jacob H, Commented May 29, 2018 at 18:54
See if this thread helps you get started: stackoverflow.com/questions/11539424/… — Jacob H
– Jacob H, Commented May 29, 2018 at 18:56
This is not a simple task because it has HUGE security ramifications. This is incredibly dangerous as sql injection is wide open here. This kind of thing is EXACTLY why stored procedures exist in the first place. OPENROWSET might help but this sounds like a classic xy problem. xyproblem.info — Sean Lange
– Sean Lange, Commented May 29, 2018 at 19:01
I agree with @SeanLange. What is the problem that inserting an extra script at the beginning of every script is meant to solve? — Ann L.
– Ann L., Commented May 29, 2018 at 19:21
@Jacob H Thanks for hint, looks like a "pure T-SQL" workaround is relatively easy: just read in the procedure and then execute it using dynamic SQL. (Should have thought of it myself, the "injected" procedure uses a fair bit of dynamic SQL.) — user1459519
– user1459519, Commented May 29, 2018 at 20:27

Ann L. · Accepted Answer · 2018-05-29 19:18:53Z

1

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.

answered May 29, 2018 at 19:18

Ann L.

14k5 gold badges39 silver badges67 bronze badges

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

1 Comment

user1459519 Over a year ago

You've essentially figured out whats going on. The queries/scripts (both mean the same thing) that are used are NOT kept as stored procedures. Instead python (which could do a "Brute Force & Ignorance" merge) reads the query from a file and passes it to SQL Server. (End-users have no access to these files and using files makes app distribution easier.) The "Martin Smith" answer to the hint provided by @Jacob H (how to read a file from within SQL), when used with dynamic SQL, should give me the workaround that I need. We'll see.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related