sql動態sql給變量復值
Before we delve into these SQL concepts, note that I like to do all my development in SQL Management Studio. So if you want to follow along go ahead and open that up.
在深入研究這些SQL概念之前，請注意，我喜歡在SQL Management Studio中進行所有開發。 因此，如果您想繼續前進，請打開它。
sql聲明變量并賦值。
Variables are extremely useful in SQL scripts. They offer the flexibility needed to create powerful tools for yourself. They are especially useful in dynamic SQL environments. They can, and are often used as counters for loops to control how many times you want the code inside the loop to run. A statement can use it to test the value of the data, or a stored procedure can use it to return a value.
變量在SQL腳本中非常有用。 它們提供了為自己創建強大工具所需的靈活性。 它們在動態SQL環境中特別有用。 它們可以并且通常用作循環的計數器，以控制您希望循環中的代碼運行多少次。 語句可以使用它來測試數據的值，或者存儲過程可以使用它來返回值。
We can use the DECLARE statement to declare one or more variables. From there we can utilize the SET command to initialize or assign a value to the variable. Here is a quick example:
復變量的正弦函數， 我們可以使用DECLARE語句來聲明一個或多個變量。 從那里我們可以利用SET命令來初始化變量或為變量賦值。 這是一個簡單的示例：
?
DECLARE @MAXRECORD INTYou can declare multiple variables in the same DECLARE statement just separate them with a comma. Example would be:
您可以在同一DECLARE語句中聲明多個變量，只需用逗號將它們分開即可。 例如：
?
/*Declare Variables*/
DECLARE @DBNAME NVARCHAR(100),@RECOVERYMODE NVARCHAR(100),@MAXRECORD INT,@CURRENTRECORD INT,@SQL NVARCHAR(MAX)That seems simple enough.
這似乎很簡單。
實變量的賦值函數， We have declared a variable called @maxrecord as an INT data type. Now we can use the SET operator to assign it a value. Like this:
我們已將名為@maxrecord的變量聲明為INT數據類型。 現在，我們可以使用SET運算符為其分配一個值。 像這樣：
?
SET @MAXRECORD = 10We just assigned the INT variable @maxrecord with a value of 10. This can change throughout a SQL script, thus providing great flexibility.
我們剛剛為INT變量@maxrecord分配了值10。這可以在整個SQL腳本中更改，從而提供了極大的靈活性。
Think of a variable as a box that acts as a place holder for whatever value you want to put in it.
復變量函數？ 可以將變量想像成一個盒子，充當要放置在其中的任何值的占位符。
The SET statement can only be used on variable at a time. This can become cumbersome if you need to assign values to multiple attributes. But you do need to use multiple set statements.
SET語句一次只能在變量上使用。 如果您需要為多個屬性分配值，則可能會變得很麻煩。 但是您確實需要使用多個set語句。
?
SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery])
SET @CURRENTRECORD = 1
SET @SQL = ''However you can use a select statement to make this process a little easier. (Largely depending on what you’re trying to accomplish). Here is a quick example of assigning values to variables in a select statement.
動態局部變量、 但是，您可以使用select語句使此過程更容易一些。 （很大程度上取決于您要完成的工作）。 這是在select語句中為變量分配值的快速示例。
?
SELECT @DBNAME = '['+[DBName]+']'
FROM [#DBRecovery]
WHERE [ROWNUM] = @CURRENTRECORDI often think of Dynamic SQL as “code that thinks”. Although it’s not quite cognitive, it can be used to handle a lot of tasks that would likely be repetitive or have a high administrative overhead. The concept of Dynamic SQL is one that allows you to build a SQL statement or a full batch of SQL code and present it as a character string. Then you can take this string and execute it. SQL Server gives us a couple of options to execute character strings as T-SQL Code. One is via the simple EXECUTE command (I like to use “EXEC”) or the sp_executesql stored procedure.
我經常將動態SQL視為“思考的代碼”。 盡管不是很容易理解，但是它可以用于處理很多可能是重復的或高管理開銷的任務。 動態SQL的概念使您可以構建一條SQL語句或一整批SQL代碼并將其顯示為字符串。 然后，您可以使用此字符串并執行它。 SQL Server為我們提供了兩種將字符串作為T-SQL代碼執行的選項。 一種是通過簡單的EXECUTE命令（我喜歡使用“ EXEC”）或sp_executesql存儲過程。
So, again why would we ever want to use Dynamic SQL? One reason would be the automation of administrative tasks. Let’s say you have a non-production server with every limited space and you want to make sure that all the databases on your server are in simple recovery mode (to minimize the growth of the transaction logs). Rather than manually query the server to identify the databases that meet this parameter and then manually set them to the proper recovery model you can write a piece of code to execute periodically. This code can identify the databases that aren’t compliant with your set rules and make then make the change for you. Thus reducing your administrative overhead. This will save you time and allow you to focus on meeting real deadlines, projects and such.
那么，為什么我們還要使用動態SQL？ 原因之一是管理任務的自動化。 假設您有一臺具有有限空間的非生產服務器，并且您想確保服務器上的所有數據庫都處于簡單恢復模式（以最大程度地減少事務日志的增長）。 您可以編寫一段代碼來定期執行，而不是手動查詢服務器以識別滿足此參數的數據庫，然后將它們手動設置為正確的恢復模型。 該代碼可以識別不符合您設置的規則的數據庫，然后為您進行更改。 從而減少您的管理開銷。 這將節省您的時間，并使您可以專注于滿足實際的截止日期，項目等。
靜態變量動態變量？ The EXEC command was around before the more robust sp_executesql stored procedure. Rather than placing Unicode strings into one of the parameters, you simply place it in parenthesis after exec.
在更強大的sp_executesql存儲過程之前，存在EXEC命令。 無需將Unicode字符串放入參數之一，只需將其放在exec后面的括號中即可。
EXEC (sqlstatement)
EXEC（sqlstatement）
You can even place the piece of code into a variable and put the variable in parenthesis. (Note that EXEC allows the use of both regular character strings and Unicode character strings as input).
java動態變量， 您甚至可以將一段代碼放入一個變量中，并將該變量放在括號中。 （請注意，EXEC允許使用常規字符串和Unicode字符串作為輸入）。
This is a great built-in stored procedure for SQL Server. It allows you to use input and output parameters allowing your dynamic SQL code to be secure and efficient. ?The Parameters not only serve a purpose for flexibility, but they also inhibit SQL Injection attacks since they appear as operands and not part of the actual code.
這是SQL Server的出色內置存儲過程。 它允許您使用輸入和輸出參數，從而使動態SQL代碼安全有效。 參數不僅起到靈活性的作用，而且還抑制SQL注入攻擊，因為它們顯示為操作數，而不是實際代碼的一部分。
Also note that as long as the SQL statement itself remains the same, yet only the parameters change, the query optimizer will utilize the same cached execution plan.
還要注意，只要SQL語句本身保持不變，但是只有參數更改，查詢優化器才會使用相同的緩存執行計劃。
sql變量， Let’s use the example above regarding databases and simple recovery model. I’m not going to write out the entire script for you here as it is beyond the scope of the article. But the snippets I provide should provide enough information to show how to use this method.
讓我們使用上面有關數據庫和簡單恢復模型的示例。 我不會在這里為您寫出整個腳本，因為這超出了本文的范圍。 但是，我提供的摘錄應提供足夠的信息以顯示如何使用此方法。
My goal is to find out if any databases are not in the simple recovery model. If they are, I want them to be put into the compliant mode.
我的目標是找出是否有任何數據庫不在簡單恢復模型中。 如果是這樣，我希望將它們置于兼容模式下。
?
/*Insert Databases names into Temp Table*/
BEGIN TRY
DROP TABLE #DBRecovery
END TRY
BEGIN CATCH SELECT 1 END CATCHSELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]),DBName = [name],RecoveryModel = [recovery_model_desc]
INTO #DBRecovery
FROM sys.[databases]
WHERE [recovery_model_desc] NOT IN ('Simple')/*Declare Variables*/
DECLARE @DBNAME NVARCHAR(100),@RECOVERYMODE NVARCHAR(100),@MAXRECORD INT,@CURRENTRECORD INT,@SQL NVARCHAR(MAX)/*Initialize Variables*/
SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery])
SET @CURRENTRECORD = 1
SET @SQL = ''/*BEGIN LOOP*/
WHILE @CURRENTRECORD <= @MAXRECORDBEGINSELECT @DBNAME = '['+[DBName]+']'FROM [#DBRecovery]WHERE [ROWNUM] = @CURRENTRECORD/*Build Command*/SET @SQL = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE'EXEC sys.[sp_executesql] @SQL/*Next Record*/
SET @CURRENTRECORD = @CURRENTRECORD + 1END
DROP TABLE [#DBRecovery]I like to take the criteria and place the databases that need to be altered into a temp table.
動態函數。 我喜歡采用標準并將需要更改的數據庫放入臨時表中。
?
SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY sys.[databases]),DBName = [name],RecoveryModel = [recovery_model_desc]
INTO #DBRecovery
FROM sys.[databases]
WHERE [recovery_model_desc] NOT IN ('Simple')As you can see, I am using variables as they play an important role in creating dynamic SQL.
如您所見，我正在使用變量，因為它們在創建動態SQL中起著重要的作用。
?
DECLARE @DBNAME NVARCHAR(100),@RECOVERYMODE NVARCHAR(100),@MAXRECORD INT,@CURRENTRECORD INT,@SQL NVARCHAR(MAX)I use SET to assign values to a few of the variables. In this case, I want the @maxrecord variable to know how many records in total we must loop through. The @currentrecord acts as our counter. Once @currentrecord is equal the @maxrecord the loop will end.
我使用SET將值分配給一些變量。 在這種情況下，我希望@maxrecord變量知道總共必須循環通過多少條記錄。 @currentrecord充當我們的計數器。 一旦@currentrecord等于@maxrecord，循環將結束。
?
/*Initialize Variables*/
SET @MAXRECORD = (SELECT MAX(ROWNUM) FROM [#DBRecovery])
SET @CURRENTRECORD = 1
SET @SQL = ''多變量分析。 I use the @SQL variable to store the actual code that will execute. In this case, it is a simple command to change the recovery model of the database. Remember we already identified which databases are not in simple mode when we created the temp table.
我使用@SQL變量存儲將執行的實際代碼。 在這種情況下，更改數據庫的恢復模型是一個簡單的命令。 記住，我們在創建臨時表時已經確定了哪些數據庫不在簡單模式下。
?
/*Build Command*/SET @SQL = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY SIMPLE'??Then finally, I use the sp_executesql stored procedure to execute the command that is stored in the @SQL variable.
最后，我使用sp_executesql存儲過程來執行存儲在@SQL變量中的命令。
?
EXEC sys.[sp_executesql] @SQLAs the loop moves onto the next record in the table the @DBNAME will store the name of a different database, thus having the command execute on a different database. This is an example of SQL code acting dynamically.
sql將查詢到的數據賦值給變量。 當循環移至表中的下一個記錄時，@ DBNAME將存儲其他數據庫的名稱，從而使命令在其他數據庫上執行。 這是動態執行SQL代碼的示例。
Another way to visualize a loop:
可視化循環的另一種方法：
翻譯自: https://www.sqlshack.com/using-variables-in-dynamic-sql/
sql動態sql給變量復值
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
