您的位置: 首页  >  技术问答  >  将 TSQL 存储在数据库中

将 TSQL 存储在数据库中

分类: 技术问答 • 2022-04-27 23:52:27
问题描述：

将 TSQL 放入数据库是个坏主意吗?例如，请参见下面的 DDL:

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..............")

然后，VB.NET 应用程序将能够选择要在运行时运行的 SQL.

A VB.NET app would then be able to choose the SQL to run at runtime.

或者，在 SQL 字段中，我可以包含存储过程的名称，而 VB.NET 应用程序可以改为执行存储过程.

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.

答

我在一些项目中也使用这种策略.如果用户无法更改数据库中的 SQL 语法，我看不到太多安全问题.如果是这样，您必须在使用 SQL 之前对其进行评估.

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.

此外，如果查询中的某些参数对于不同的行是不同的，您可以存储这些参数并在存储过程中运行它们，或者从这些值创建动态 SQL.

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.

我用它来收集数据并将数据传输到另一种数据格式.源数据以非常不同的数据格式和数据库设计存储.目标格式始终是相同的 EAV 模型.

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.

我将存储的 SQL 作为整个 SQL 语句的一部分，用于传输一批一天数据的数据.

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.

我还想存储每批数据使用的 SQL 语句的历史记录，以备将来更改 SQL 语句时参考.

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.

使用 SP 的选项仍然可行，尽管我现在不知道一种方法来存储随时间使用的 SQL 语句的历史记录.

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.

相关推荐