201210212122SQL - 哇 EXECUTE 在 SQL SERVER 2012 進化了

在 SQL SERVER 裡 EXECUTE 這個指令大家絕對不陌生,在 SQL SERVER 2012 中,EXECUTE 它進化了(跟神奇寶貝一樣...XD),怎麼說呢!?
以往在執行預存程序時,回傳的欄位名稱是定義在預存程序中的,但現在可以透過 EXECUTE 所提供的 WITH RESULT SETS 來自定義名稱,以及轉換其資料類型,當然也是有所限制的,但這已經是不錯的進化了,以下就以範例來呈現吧

建立一個預存程序
CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
 SET NOCOUNT ON;

 SELECT TOP 5 [CategoryID], [CategoryName], [Description]
 FROM dbo.Categories;

 SELECT TOP 5 [CustomerID], [CompanyName], [Phone]
 FROM dbo.Customers;

END
GO

以下是範例

--SQL SERVER 2012 以前的做法
EXECUTE [dbo].[MyProcedure]

--SQL SERVER 2012 EXECUTE 進化後提供的新寫法
EXECUTE [dbo].[MyProcedure]
WITH RESULT SETS
(
 (
  C1_ID INT,
  C1_NAME NVARCHAR(15),
  C1_DESC NTEXT
 ),
 (
  C2_ID NCHAR(5),
  C_COMP NVARCHAR(30),
  C_PH NVARCHAR(24)
 )
);

--轉換 C1_ID 的資料類型
EXECUTE [dbo].[MyProcedure]
WITH RESULT SETS
(
 (
  C1_ID NCHAR(100),
  C1_NAME NVARCHAR(15),
  C1_DESC NTEXT
 ),
 (
  C2_ID NCHAR(5),
  C_COMP NVARCHAR(30),
  C_PH NVARCHAR(24)
 )
);

--定義一個新的資料表來做為輸出時用的呈現名稱
CREATE TABLE MyTestTable
(
 C1_ID INT,
 C1_NAME NVARCHAR(15),
 C1_DESC NTEXT
)

EXEC [dbo].[MyProcedure]
WITH RESULT SETS
(
 AS object [Northwind].[dbo].[MyTestTable],
 (
  C2_ID NCHAR(5),
  C_COMP NVARCHAR(30),
  C_PH NVARCHAR(24)
 )
)

結果:


以下範例是透過程式的方式來做執行

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            ExecuteStoredProcedures();
            Console.ReadKey();
        }

        private static void ExecuteStoredProcedures()
        {
            using (SqlConnection sc = new SqlConnection(@"Data Source=xxx;Initial catalog=Northwind;uid=sa;pwd=xxx"))
            {
                sc.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = sc;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = @"EXECUTE [dbo].[MyProcedure]
                        WITH RESULT SETS
                        (
                         (
                          C1_ID INT,
                          C1_NAME NVARCHAR(15),
                          C1_DESC NTEXT
                         ),
                         (
                          C2_ID NCHAR(5),
                          C_COMP NVARCHAR(30),
                          C_PH NVARCHAR(24)
                         )
                        )";

                    SqlDataReader sqlReader = cmd.ExecuteReader();

                    while (sqlReader.Read())
                    {
                        Console.WriteLine("Categories Data - " + sqlReader["C1_ID"] + " - " + sqlReader["C1_NAME"] + " - " + sqlReader["C1_DESC"]);
                    }

                    sqlReader.NextResult();

                    while (sqlReader.Read())
                    {
                        Console.WriteLine("Customers Data - " + sqlReader.GetValue(0) + " - " + sqlReader.GetValue(1) + " - " + sqlReader.GetValue(2));
                    }
                }
            }
        }
    }
}

結果:


參考:
EXECUTE (Transact-SQL)
SQL Server2012新特性WITH RESULT SETS
C# Multiple Result Sets
Execute with Result Sets new option in Denali
SQL Server 2012 - WITH RESULT SETS

沒有上一則|日誌首頁|沒有下一則
回應
關鍵字
累積 | 今日
loading......