TSQL storage in the database

Question

Is it OK to put TSQL in a database? For example, see below DDL:

CREATE TABLE dbSystems (ID INT NOT NULL IDENTITY, Description VARCHAR(100), SQL (10000)) INSERT INTO dbSystems ('Sales System', 'DECLARE SalesVariable int..............") INSERT INTO dbSystems ('Finance System', 'DECLARE FinanceVariable int..............") INSERT INTO dbSystems ('Production System', 'DECLARE ProductionVariable int..............") 

VB.NET application will be able to select SQL to run at runtime.

Alternatively, in the SQL field, I could contain the name of the stored procedure, and the VB.NET application could execute the stored procedure.

Answer 1

I use this tactic in some projects. If users cannot change the SQL syntax in the database, I don’t see a big security problem. If so, you should evaluate SQL before using it.

Also, if only a few parameters in the query differ for different rows, you can simply save them and run them in a stored procedure or create dynamic SQL from the values.

I used this to collect and transfer data to another data format. The source data was stored in very different data formats and database designs. The target format has always been the same EAV model.

I used stored SQL as part of an entire SQL statement that transfers data from one batch of data in one day.

Since I did not want the mess of stored procedures to increase over time, I selected this option.

I also wanted to keep a history of the SQL statements used for each batch of data for future reference when I changed SQL queries in the future.

The ability to use SP will continue to be viable, although I don’t know how to do it now to keep a history of SQL statements used over time.

Answer 2

This is such a good idea that Microsoft has already implemented it for you, as stored procedures. Take a look at sys.all_sql_modules :

 object_id int ID of the object of the containing object. Is unique within a database. definition nvarchar(max) SQL text that defines this module. 

All your application needs to provide is the name of the executable stored procedure, and SQL Server will look for that SQL and execute it.