t-sql執行結果
Context
語境
Back in my days at school, I followed a course entitled “Object-Oriented Software Engineering” where I learned some “design patterns” like Singleton and Factory. If you are not familiar with this expression, here is a definition of a design pattern from Wikipedia:
回到學校時，我參加了一個名為“面向對象軟件工程”的課程，在那里我學習了一些“設計模式”，例如Singleton和Factory。 如果您不熟悉此表達式，請參見Wikipedia中的設計模式定義：
“In?software engineering, a?software design pattern?is a general reusable solution to a commonly occurring problem within a given context in?software design. It is not a finished design that can be transformed directly into?source?or?machine?code. It is a description or template for how to solve a problem that can be used in many different situations. Design patterns are formalized?best practices?that the programmer can use to solve common problems when designing an application or system.”
“在軟件工程中 ， 軟件設計模式是針對軟件設計中給定上下文中常見問題的通用可重用解決方案。 它不是可以直接轉換為源代碼或機器代碼的最終設計。 它是關于如何解決可以在許多不同情況下使用的問題的描述或模板。 設計模式是形式化的最佳實踐 ，程序員可以在設計應用程序或系統時用來解決常見問題。”
Well, as stated above, these design patterns are not a finished design, in and of itself, and must be written in the programming language of our choice. Transact SQL is also a programming language and we could also imagine the implementation of those design patterns for data generation. Actually, the subject of this article can be seen as a specialized version of the Command design pattern.
嗯，如上所述，這些設計模式本身并不是完成的設計，必須使用我們選擇的編程語言編寫。 Transact SQL也是一種編程語言，我們還可以想象這些設計模式的實現以生成數據。 實際上，本文的主題可以看作是Command設計模式的專門版本。
The particular design pattern this article is about can be used in multiple situations and we will review it in details in a few seconds.
本文涉及的特定設計模式可以在多種情況下使用，我們將在幾秒鐘內對其進行詳細審查。
In following sections, we will focus on the problem that we want to solve and on the general solution to that problem. This solution constitutes therefore a design pattern for the solution. Once we know this design pattern, we can specialize it to fulfill the needs of a particular situation and that’s what we’ll do in last section.
在以下各節中，我們將集中于我們要解決的問題和該問題的一般解決方案。 因此，該解決方案構成了該解決方案的設計模式。 一旦知道了這種設計模式，就可以對其進行專門化以滿足特定情況的需求，這就是我們在上一節中要做的。
The problem
問題
Now, it’s time to talk a little bit about the problem we want to solve in this article. I’ve been confronted to it more than once and I’m pretty sure that you’re are not stranger to it neither.
現在，該討論我們要在本文中解決的問題了。 我已經不止一次面對它，而且我很確定你對它也不陌生。
Let’s say we want to develop an application running T-SQL commands that will insert new records into a set of tables. This application can be a script generator, a monitoring or reporting tool or anything else you could imagine. We would like to be able to get back all data related to a particular execution of this application. Why?would we do that? There can be multiple answers:
假設我們要開發一個運行T-SQL命令的應用程序，該命令會將新記錄插入到一??組表中。 該應用程序可以是腳本生成器，監視或報告工具或您可以想象的任何其他工具。 我們希望能夠檢索與該應用程序的特定執行相關的所有數據。 我們為什么要這樣做？ 可能有多個答案：
With this approach, we can divide complex code into smaller pieces in which the business logic is less complex and easier to manage. As we divide these pieces of code, we could also run the processing asynchronously.
使用這種方法，我們可以將復雜的代碼分成較小的部分，其中業務邏輯不太復雜且更易于管理。 在劃分這些代碼段時，我們還可以異步運行處理。
The following shows an example process in which we could eventually use the solution defined in this article. Green arrows mean that source process has to succeed for destination process to run. The arrow itself means that data generated from source process is used as input (or loaded) by destination process.
下面顯示了一個示例過程，在該過程中我們最終可以使用本文定義的解決方案。 綠色箭頭表示源進程必須成功才能運行目標進程。 箭頭本身意味著從源進程生成的數據被目標進程用作輸入（或加載）。
Our application can be written in another language but the solution to the problem has to be written in T-SQL because the application can be composed of multiple executables and we don’t want to write custom libraries to include in each individual executable.
我們的應用程序可以用另一種語言編寫，但是問題的解決方案必須用T-SQL編寫，因為該應用程序可以由多個可執行文件組成，并且我們不想編寫包含在每個單獨的可執行文件中的自定義庫。
The solution
解決方案
Now, let’s discuss some points that will lead us towards the entire solution for purpose better understanding. We will segment this solution into two distinct parts: the “what” and the “how”. The “what” consists in the objects we need while the “how” is the way we will use these objects. Using both together constitutes the actual solution to the presented problem.
現在，讓我們討論一些要點，這些要點將引導我們走向整個解決方案，以更好地理解目的。 我們將解決方案分為兩個不同的部分：“什么”和“如何”。 “什么”包含我們需要的對象，而“如何”則是我們使用這些對象的方式。 兩者一起使用構成了所提出問題的實際解決方案。
What do we need?
我們需要什么？
The biggest need that we can understand from problem explanation is that we need to uniquely identify an execution of the application. So, we need to assign a unique identifier during execution. In terms of implementation. This unique identifier can be either:
從問題解釋中我們可以理解的最大需求是，我們需要唯一地標識應用程序的執行。 因此，我們需要在執行期間分配一個唯一的標識符。 在執行方面。 此唯一標識符可以是：
As we need to be able to refer to an execution of the application and get back the results of that execution, this unique identifier must be stored in a table. We’ll refer to that table as the “execution log”.
由于我們需要能夠引用應用程序的執行并獲取該執行的結果，因此該唯一標識符必須存儲在表中。 我們將該表稱為“執行日志”。
But we can’t only log unique identifiers into that table because we might need to read other information to know which one we should use for further processing. You will find below a list of information I tend to keep in order to get back to a particular execution of an applications. This list is not static and you can either remove or add one or more items to it.
但是，我們不能僅將唯一標識符記錄到該表中，因為我們可能需要讀取其他信息才能知道應該使用哪個標識符進行進一步處理。 您將在下面找到我傾向于保留的信息列表，以便返回到特定的應用程序執行情況。 此列表不是靜態的，您可以刪除或添加一個或多個項目。
You can look at following T-SQL statement that corresponds to the creation statement for the ExecutionLog table using previous list.
您可以使用上一個列表查看對應于ExecutionLog表的創建語句的以下T-SQL語句。
?
CREATE TABLE [ExecutionLog] ([ExecutionId]?????????? <DATATYPE_TO_BE_DEFINED> NOT NULL,[ExecutionName]???????? VARCHAR(512)??NOT NULL,[SQLLogin]??????????????VARCHAR(512)??NOT NULL,[ClientNetAddress]??????VARCHAR(128)??NOT NULL,[ClientHostName]????????VARCHAR(128)??NOT NULL,[ClientProgramName]???? VARCHAR(256)??NOT NULL,[StartTime]???????????? DATETIME2???? NOT NULL,[EndTime]?????????????? DATETIME2???? NULL,[ExecutionParams]?????? VARCHAR(MAX)??NULL,[Outcome]?????????????? VARCHAR(64)?? NULL,[ErrorDetails]??????????VARCHAR(MAX)??NULL
)With this information, we really can present, to a user, a subset of the entire execution log with only its own actions. We can also stop a cascading process by checking the execution success information: if the execution was not successful and we needed the results of this execution to perform an additional treatment on this, then it’s of no use to carry on…
有了這些信息，我們實際上可以僅通過自己的操作向用戶呈現整個執行日志的子集。 我們還可以通過檢查執行成功信息來停止級聯過程：如果執行不成功，并且我們需要執行結果來對此進行其他處理，那么繼續進行就沒有用了……
How will we do it?
我們將如何做？
At this point, we’ve answered the “what” question: we exactly know what we need to address the challenge defined in this article. Now, let’s talk about the “how”: how will we make it happen?
在這一點上，我們已經回答了“什么”問題：我們完全知道我們需要解決本文定義的挑戰。 現在，讓我們談談“如何”：我們將如何實現它？
We could write a set of T-SQL queries inside the application that will use the execution log, but this means that if we want to change a piece of code in these queries, we need to change it in every executable that works with this design pattern. There is, to me, a better solution to that problem.
我們可以在應用程序內編寫一組使用執行日志的T-SQL查詢，但這意味著，如果我們想在這些查詢中更改一段代碼，則需要在與此設計兼容的每個可執行文件中進行更改。模式。 對我來說，有一個更好的解決方案。
Instead, we should create a stored procedure that will generate the unique identifier and store the information discussed above. We will call this stored procedure “LogNewExecution“. Here is the interface for such a function. Again, this can be customized to your needs.
相反，我們應該創建一個存儲過程，該過程將生成唯一標識符并存儲上面討論的信息。 我們將這個存儲過程稱為“ LogNewExecution ”。 這是此功能的接口。 同樣，可以根據您的需求進行定制。
?
PROCEDURE [LogNewExecution] (???? @ExecutionName????????????VARCHAR(512),???? @StartTime????????????????DATETIME2,???? @ExecutionId??????????????<DATA_TYPE> OUTPUT,???? @Debug????????????????????BIT = 0??-- I always add @Debug parameter to my procedures-- to get extended information at execution
)When the application runs, it will call that stored procedure and the application will get back the unique identifier of its execution thanks to the @ExecutionId parameter.
當應用程序運行時，它將調用該存儲過程，并且由于@ExecutionId參數，該應用程序將獲取其執行的唯一標識符。
The body of this procedure should contain a modified version of following query. This query essentially gets back information about caller and inserts it in conjunction with the value of @ExecutionName and @StartTime parameters.
該過程的正文應包含以下查詢的修改版本。 該查詢實質上是獲取有關調用方的信息，并將其與@ExecutionName和@StartTime參數的值一起插入。
?
INSERT into [ExecutionLog] (ExecutionName, SQLLogin, ClientNetAddress, ClientHostName, ClientProgramName, StartTime
)
SELECT@ExecutionName,s.login_name,c.client_net_address,s.[host_name],s.program_name,ISNULL(@StartTime,SYSDATETIME())
from sys.dm_exec_sessions s
left join sys.dm_exec_connections c
on s.session_id = c.session_id
WHERE s.session_id = @@spid
;As you can see in sample code above, there is no value inserted for ExecutionParams column of ExecutionLog table. Actually, we could consider that an update should be run in application as we could log execution even before parameter validation. If it might not happen, you should add a @ExecutionParams parameter to LogNewExecution stored procedure.
如您在上面的示例代碼中看到的，沒有為ExecutionLog表的ExecutionParams列插入任何值。 實際上，我們可以考慮在應用程序中運行更新，因為即使在參數驗證之前也可以記錄執行情況。 如果可能不會發生，則應在LogNewExecution存儲過程中添加@ExecutionParams參數。
When application execution completes, no matter the outcome, the application will simply run an update against the ExecutionLog table to set values to the appropriate columns regarding the outcome.
應用程序執行完成后，無論結果如何，應用程序都將僅對ExecutionLog表運行更新，以將值設置為與結果相關的適當列。
Following diagram summarizes what we discussed so far.
下圖總結了到目前為止我們討論的內容。
We will now review two implementations of that design pattern that I use very often. As you will see, each implementation is specialized to the situation it covers. Let’s just say for now that the first implementation is designed to be used in the context of reporting and the second in the context of script generation. We could also imagine other use cases like a monitoring and alerting tool, for example.
現在，我們將回顧我經常使用的該設計模式的兩種實現。 正如您將看到的，每種實現都針對其所涉及的情況進行了專門化。 現在，讓我們先說一下，第一個實現被設計為在報告的上下文中使用，第二個實現被設計為在腳本生成的上下文中使用。 我們還可以想象其他用例，例如監視和警報工具。
Implementation I: Generic Reporting Execution Log
實施方式I：通用報告執行日志
Before getting into the details of the design pattern implementation, let’s review what needs it will cover.
在詳細介紹設計模式實現之前，讓我們回顧一下它將滿足的需求。
A while ago, I implemented an extended deadlock monitoring process that gets back deadlock XML graphs from Extended Events, stores it into a table and parses the XML to store extended details into another table. As soon as this process ends, we can run various reports as the occurrences by date or the most commonly implied application and so on. But we need to record these reports because we can’t keep all the collected deadlock XML graphs for obvious space consumption considerations.
不久前，我實現了擴展的死鎖監視過程，該過程從擴展事件中獲取死鎖XML圖，將其存儲到表中，然后解析XML以將擴展的詳細信息存儲到另一個表中。 該過程結束后，我們可以按日期或最隱含的應用程序等形式運行各種報告。 但是我們需要記錄這些報告，因為我們不能出于明顯的空間消耗考慮而保留所有收集的死鎖XML圖。
But we could plan other kinds of reports than just deadlock reports, so let’s review some general facts about reporting:
但是我們可以計劃其他報告，而不僅僅是死鎖報告，因此讓我們回顧一下有關報告的一些一般事實：
This implies that output type and output path information have to be kept somewhere so that we are able to actually retrieve reports contents and process it.
這意味著輸出類型和輸出路徑信息必須保留在某處，以便我們能夠實際檢索報告內容并進行處理。
Based on the description above, I specialized the design pattern presented in this article as follows.
基于以上描述，我專門介紹了本文中介紹的設計模式，如下所示。
Firstly, I extended the ExecutionLog table to add a ReportClass column so that I can filter records to only get reports related to deadlocks or whatever. I also added an OutputType and an OutputPath column. Here are two example usages of those two columns:
首先，我擴展了ExecutionLog表以添加一個ReportClass列，以便可以過濾記錄以僅獲取與死鎖或其他相關的報告。 我還添加了OutputType和OutputPath列。 這是這兩列的兩個示例用法：
In addition to those changes, I needed to define the way to uniquely identify a report. To do so, I simply used an IDENTITY column. I used BIGINT data type just in case I would use it a lot in future days.
除了這些更改之外，我還需要定義唯一標識報告的方式。 為此，我只使用了IDENTITY列。 我使用了BIGINT數據類型，以防萬一我將來會經常使用它。
As I don’t think extended explanations on those columns are necessary, you will find below the T-SQL statement to create the actual ExecutionLog table:
由于我認為不需要在這些列上進行擴展說明，因此您將在T-SQL語句下面找到創建實際的ExecutionLog表的方法：
?
CREATE TABLE [Reporting].[ExecutionLog] ([ReportId]??????????????BIGINT????????NOT NULL IDENTITY(1,1),[ReportClass]?????????? VARCHAR(512)??NULL,[ReportName]????????????VARCHAR(512)??NOT NULL,[SQLLogin]??????????????VARCHAR(512)??NOT NULL,[ClientNetAddress]??????VARCHAR(128)??NOT NULL,[ClientHostName]????????VARCHAR(128)??NOT NULL,[ClientProgramName]???? VARCHAR(256)??NOT NULL,[StartTime]???????????? DATETIME2???? NOT NULL,[EndTime]?????????????? DATETIME2???? NULL,[OutputType]????????????VARCHAR(32)?? NULL, -- TABLE / FILE / NONE[OutputPath]????????????VARCHAR(MAX)??NULL,[ReportParams]??????????VARCHAR(MAX)??NULL,[Outcome]?????????????? VARCHAR(64)?? NULL,[ErrorDetails]??????????VARCHAR(MAX)??NULL
)I also changed a little bit the LogNewExection procedure so that it takes the three new columns (ReportClass, OutputType and OutputPath).
我還稍微修改了LogNewExection過程，以便它采用了三個新列（ ReportClass ， OutputType和OutputPath ）。
?
ALTER PROCEDURE [Reporting].[LogNewExecution] (@ReportClass????????????????????VARCHAR(512) = NULL,@ReportName???????????????????? VARCHAR(512),@StartTime??????????????????????DATETIME2????= NULL,@OutputType???????????????????? VARCHAR(32),@OutputPath???????????????????? VARCHAR(MAX) = NULL,@ReportId?????????????????????? BIGINT OUTPUT,@Debug??????????????????????????BIT = 0
)You will find attached to this article the script to create the schema, table and stored procedure presented in this section. You can run it against a test/dev database and test if it actually works as expected using following script.
您將在本文中找到本節中介紹的用于創建模式，表和存儲過程的腳本。 您可以針對測試/開發數據庫運行它，并使用以下腳本測試它是否按預期運行。
?
-- simulate application start
DECLARE @ReportId???? BIGINT;
DECLARE @ProcSuccess??BIT = 0;
DECLARE @LineFeed???? CHAR(2) = CHAR(13) + CHAR(10);
DECLARE @LogMsg?????? VARCHAR(MAX);
DECLARE @ProcParams?? VARCHAR(MAX);EXEC [Reporting].[LogNewExecution] @ReportName??= 'TEST - Execution',@ReportClass = NULL,@ReportId????= @ReportId OUTPUT,@OutputType??= 'TABLE',@OutputPath??= '[master].[dbo].[ThisIsATestTbl]',@Debug?????? = 1
; -- For testing purpose :
SELECT * FROM Reporting.ExecutionLog ;-- Business logic code : parameters validation
-- Could set @ProcParams value on the flySET @ProcParams = '...' ;-- update ExecutionLog with process parameters
-- I didn't add this to LogNewExecution stored procedure as
-- this could be run before the end of parameter validationUPDATE Reporting.ExecutionLog
SET ReportParams = @ProcParams
WHEREReportId = @ReportId
;-- For testing purpose:
SELECT * FROM Reporting.ExecutionLog ;BEGIN TRY-- Procedure actual action (stores in report target)-- ...SET @ProcSuccess = 1;
END TRY
BEGIN CATCHSELECT @LogMsg = '/* ----------------------------- ( Details of the error caught during execution ) -----------------------------*/' + @LineFeed +'Error #' + CONVERT(VARCHAR(10),ERROR_NUMBER()) + ' with Severity ' + CONVERT(VARCHAR(10),ERROR_SEVERITY()) + ', State ' + CONVERT(VARCHAR(10),ERROR_STATE()) + @LineFeed +'in stored procedure ' + ISNULL(ERROR_PROCEDURE(),ISNULL(OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),'N/A')) + @LineFeed +'Message:' + @LineFeed +ISNULL(ERROR_MESSAGE(),'N/A') + @LineFeed +'/* -------------------------- ( End of Details of the error caught during execution ) --------------------------*/' + @LineFeed;RAISERROR(@LogMsg,10,1);END CATCHUPDATE Reporting.ExecutionLog
SET Outcome = CASE WHEN @ProcSuccess = 1 THEN 'SUCCESS' ELSE 'ERROR' END,ErrorDetails = @LogMsg,EndTime??????= SYSDATETIME()
WHERE ReportId = @ReportId
;-- For testing purpose:
SELECT * FROM Reporting.ExecutionLog ;Here are the contents of the ExecutionLog table after execution began:
開始執行后，這是ExecutionLog表的內容：
Here are the contents of the ExecutionLog table after a failed execution:
這是執行失敗后ExecutionLog表的內容：
Finally, the contents of the ExecutionLog table after a successful execution:
最后，成功執行后， ExecutionLog表的內容：
If we want to check what would happen if an error occurred, we can change following line:
如果我們想檢查如果發生錯誤會發生什么，我們可以更改以下行：
?
SET @ProcSuccess = 1;By
通過
?
RAISERROR('Test Error',12,1)?;And we will get following view (that I split for better reading)
我們將獲得以下觀點（為了更好地閱讀，我進行了拆分）
As we can see, the outcome is set to ERROR for second report. If we look at the ErrorDetails column, we see following message:
如我們所見，第二份報告的結果設置為ERROR 。 如果我們查看ErrorDetails列， 則會看到以下消息：
Implementation II: Generic script Generation Execution Log
實施二：通用腳本生成執行日志
Here, the aim is little different. I designed this execution log when I had to migrate databases from onr server to another for multiple SQL Server instances. The aim was initially to upgrade to a newer version of both Microsoft Windows Server and Microsoft SQL Server. Based on the criticality of machine and SQL Server edition, I could migrate via backup-restore, detach-attach or by setting up a database mirroring between source and destination then failover. Other migration solutions might come up in the future.
在這里，目標幾乎沒有什么不同。 當我不得不將數據庫從另一臺服務器遷移到另一個SQL Server實例時，我設計了該執行日志。 最初的目的是升級到Microsoft Windows Server和Microsoft SQL Server的較新版本。 基于計算機和SQL Server版本的重要性，我可以通過備份-還原，分離-附加或通過在源和目標之間建立數據庫鏡像然后進行故障轉移來進行遷移。 將來可能會出現其他遷移解決方案。
While I could have written a bunch of scripts that I just call to run a particular aspect of migration, I wanted to keep control over what is done during migration and also to limit the possibilities if somebody eventually takes the scripts and runs it on a wrong server, some time after the original migration happened. So, I preferred to create a script generator. I divided the process into three steps:
雖然我可以編寫一堆腳本來運行遷移的某個特定方面，但我想保持對遷移期間所做操作的控制，并且還希望限制某些人最終采用腳本并以錯誤方式運行它的可能性。服務器，原始遷移發生后的一段時間。 因此，我更喜歡創建一個腳本生成器。 我將過程分為三個步驟：
Based on the explanations above, we can quickly identify columns that have to be added to the ExecutionLog table presented as design pattern. There is actually only one column: ValidForNbDays. While this value can be seen as another parameter and could be put into the ExecutionParams column, it’s actually relevant to add this column as we want to easily check if the generated scripts would still be valid.
根據上面的解釋，我們可以快速確定必須添加到以設計模式顯示的ExecutionLog表中的列。 實際上只有一列： ValidForNbDays 。 盡管可以將該值視為另一個參數，并且可以將其放入ExecutionParams列中，但是添加此列實際上是相關的，因為我們希望輕松檢查生成的腳本是否仍然有效。
You will find below the table that I use for this.
您將在我用于此的表格下面找到。
?
CREATE TABLE [SQLGeneration].[ExecutionLog] ([GenerationId]??????????BIGINT IDENTITY(1,1),[PurposeName]?????????? [VARCHAR](512)??NOT NULL,[SQLLogin]??????????????[VARCHAR](512)??NOT NULL,[ClientNetAddress]??????[VARCHAR](128)??NOT NULL,[ClientHostName]????????[VARCHAR](128)??NOT NULL,[ClientProgramName]???? [VARCHAR](256)??NOT NULL,[StartTime]???????????? [DATETIME]??????NOT NULL,[EndTime]?????????????? [DATETIME]??????NULL,[ValidForNbDays]????????INT???????????? NULL,[GenerationParams]??????[VARCHAR](MAX)??NULL,[Outcome]?????????????? [VARCHAR](64)?? NULL,[ErrorDetails]??????????[VARCHAR](MAX)??NULL
)I also added a @NbDaysOfValidity to LogNewExecution stored procedure.
我還向LogNewExecution存儲過程添加了@NbDaysOfValidity 。
We could review a testing batch like we did for previous example, but we won’t as it’s trivial to adapt this code to current implementation.
我們可以像前面的示例一樣檢查一個測試批處理，但是我們不會這樣做，因為將代碼修改為當前的實現很簡單。
There are a lot of use cases that come to my mind in the field of generation that could use this table:
在生成領域，我想到了很多可以使用此表的用例：
In this article, we’ve seen a standardized way to log execution of our processes. We’ve seen with two examples that it can be specialized to multiple fields and used in various situations.
在本文中，我們已經看到了記錄過程執行情況的標準化方法。 通過兩個示例，我們可以看到它可以專門用于多個領域，并可以在各種情況下使用。
翻譯自: https://www.sqlshack.com/a-t-sql-design-pattern-for-logging-process-execution/
t-sql執行結果
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
![[Matlab]求解线性方程组](/upload/rand_pic/2-402.jpg)
