問題描述

將 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.

這篇關于將 TSQL 存儲在數據庫中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！