Is it a bad idea to put TSQL in a database? For example, please see the DDL below:
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..............")
A VB.NET app would then be able to choose the SQL to run at runtime.
Alternatively, in the SQL field I could contain the name of a stored procedure and the VB.NET app could execute the stored procedure instead.
It's such a good idea that Microsoft 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 that your application needs to supply is the name of the stored procedure to execute, and SQL Server will look up that SQL and execute it.
I use this tactic too in some projects.
If users cannot change the SQL syntax in the database, I don't see much of an security issue.
If they do you have to evaluate the SQL before using it.
Also if just some parameters in the query are different for the different row, you can just store those and run them in a stored procedure, or create dynamic SQL from the values.
I have used this for collecting and transferring data to another data format. The source data was stored in very different data formats and database designs. The target format was always the same EAV model.
I used the stored SQL as a part of the entire SQL statement that transfers the data of one batch of one day data.
Since I did not want a clutter of stored procedures that would increase in amount over time I chose this option.
Also I wanted to store a history of the SQL statements used per batch of data, for future reference when I changed the SQL statements in the future.
The option of using SP would still be viable, all though I don't know a way right now to store the history of SQL statements used over time.
