Sql Server系列：使用Transact-SQL編程

1、變數

　　T-SQL變數是由declare命令聲明的。聲明時，需要declare命令的後面指定要聲明的變數名及其資料類型。可以使用的資料類型包括create table命令中的所有資料類型，以及table和SQLvariant資料類型。在單個declare命令中聲明多個變數時，需要使用逗號將它們相互隔開。

1.1、變數的預設值和範圍

　　變數的範圍（即可以使用變數的應用程式和變數的生命週期）只能是當前的批處理。新聲明的變數預設值為空白值，在運算式中使用它們之前必須為它們賦初值。

　　下面的腳步建立了兩個測試變數，並展示了它們的初值和範圍。它由兩個批處理構成的(由go相互隔開)，但它們卻屬於同一個執行過程。

DECLARE @Test INT,        @TestTwo NVARCHAR(25)SELECT @Test, @TestTwoSET @Test = 1SET @TestTwo = 'a value'SELECT @Test, @TestTwoGOSELECT @Test, @TestTwo

　　執行結果如下：

　　第一個select返回了兩個空值。在為這個兩個變數賦值之後，第二個select正確地顯示了這兩個變數的新值。當這個批處理結束後(因為遇到了批處理的結束標誌go)，所有變數的生命週期也就結束了。因此，最後一個select語句就返回了137號錯誤訊息。

1.2、使用set和select命令

　　set和select命令都可以使用運算式為變數賦值。它們之間的主要區別在於：select可以從表、子查詢或者視圖中檢索資料，並且也可以包含其他的select字句；而set命令則只能從運算式中擷取資料。在set和select命令中都可以使用函數。

　　select語句可以檢索多列，而每個列中的資料都可以賦值給一個變數。如果select語句返回了多個行，將會把結果集中最後一行的資料賦值給變數，系統不會報告任何的錯誤。

DECLARE @TempID INT,        @TempName NVARCHAR(25)SET @TempID = 100SELECT @TempID = PersonID,       @TempName = PersionNameFROM PersionSELECT @TempID, @TempName

　　如果select語句沒有返回任何行，那麼它就不會改變變數的值。

1.3、條件select

　　因為select語句可以包含where字句，所以可以使用下面的文法為變數賦值：

SELECT @Variable = expression WHERE BooleanExpression

　　where字句的作用就像條件if語句一樣。如果布林運算式為真，就會執行select。如果為假，就不會執行select；因為沒有執行select語句，當然就不會改變@Variable的值。

1.4、在SQL查詢中使用變數

DECLARE @ProductNo CHAR(10)SET @ProductNo = '10000'SELECT ProductName FROM Product WHERE ProductNo = @ProductNo

2、流程式控制制

2.1、IF

 　　一個IF只能控制一條命令。

IF Condition    Statement

2.2、Begin/End

　　一個if命令只能控制一條語句的執行與否，缺乏實用性。為解決這個問題，可以使用begin/end塊，它可將多條命令作為一個整體構成if命令的下一條命令。

IF Condition    Begin        Multiple lines    End

1>、IF Exists()

　　if exists()結構根據SQL select命令返回的結果集是否包含有進行來行判斷。因為它只需要查看結果集中有沒有行，所以在為它編寫的select語句時應當檢索全部的列(select *)。與檢查@@rowcount>0的條件相比，這種方法的速度更快，因為它在判斷時並不需要知道結果集中行的總數，只需要判斷結果集中是否有行就可以。在if exists()中只要查詢返回了一條記錄，就可以停止執行查詢，轉而執行批處理中的其他語句。

IF EXISTS(SELECT * FROM [ORDER] WHERE [ORDERID]=100)    BEGIN        PRINT 'Process Orders'    END

　　與在exists()函數中只是要select選擇主鍵的方式相比，使用select * 的方法更好哦，這是由於兩個原因造成的，首先，使用*的速度更快，因為此時SQL Server可以選擇最快的索引來執行查詢。其次，根據SQL Server的不同Service Pack層級，對於使用GUID作為主鍵的表來說，使用select選擇主鍵的方法可能會失敗。

2>、IF/ELSE

　　else命令是可選的，只有當if條件的計算結果為假時，才會執行放在else命令後面的語句。

3、暫存資料表和表變數

3.1、局部暫存資料表

　　建立暫存資料表的方法與建立使用者定義表的方式基本相同，只是在建立暫存資料表時必須使用有#號開頭的表名。暫存資料表的生命週期很短暫。當建立它的批處理或過程結束時，暫存資料表就被刪除了。

CREATE TABLE #ProductTemp(    ProductID INT PRIMARY KEY       )

3.2、全域暫存資料表

　　所有使用者均可以引用全域暫存資料表，只有當最後一個引用它的會話結束之後才會將它刪除。建立全域暫存資料表，需要使用由兩個##開頭的表名(##TableName)。

3.3、表變數

　　表變數類似於暫存資料表，其優點在於它只存在記憶體之中。表變數與變數具有同樣的範圍和生命週期，只有建立它們的批處理、過程或者函數才能夠看到它們。當這些批處理、過程或者函數結束的時候，相應的表變數也就不再存在了。

DECLARE @WorkTable TABLE (    ID INT PRIMARY KEY,    Name VARCHAR(50)      )INSERT INTO @WorkTable(ID,Name) VALUES (1,'1000')SELECT ID,Name FROM @WorkTable

3.4、動態SQL

　　動態SQL特別適合於完成以下工作：

　　◊ 利用多個查詢條件來建立定製的where字句

　　◊ 根據where字句的內容，建立定製的from字句，使其只包含所需要的表和串連

　　◊ 根據使用者要求，動態地建立不同的order by字句，按照不同的方式對資料進行排序

1>、執行動態SQL

EXEC[UTE] (T-SQL batch)WITH RECOMPILE

　　使用with recompile選項可以強制SQL Server重新編譯指定的T-SQL批處理，而不要使用以前執行過的查詢執行計畫。如果T-SQL字串及參數變化較大，使用with recompile選項可以防止SQL Server使用與其不匹配的查詢計劃，從而造成效能的降低。但如果所執行的T-SQL字串都是類似的語句，就不需要使用with recompile選項重新編譯，這反而會降低效能。由於絕大多數動態SQL過程都會建立極為不同的SQL語句，所以在一般情況下使用with recompile選項都是合適的。

EXEC('SELECT * FROM Product WHERE ProductID=10')

2>、SP_EXECUTESQL

EXEC SP_EXECUTESQL    'T-SQL' query    Parameters Definition    Parameter,Parameter,...

　　T-SQL的SQL語句和參數定義必須使用Unicode字串。

　　使用參數可以最佳化效能。如果在每次只需T-SQL語句時都使用同樣的參數，就應該使用SP_EXECUTESQL以及相應的參數來執行它，這樣做可以儲存查詢計劃，在今後執行該語句的時候就會最佳化效能。

EXEC SP_EXECUTESQL    N'SELECT ProductName       FROM Product       WHERE ProductID=@ProductID',    N'@ProductID INT',    @ProductID=10