呼叫預存程序是 By Name 還是 By Position 傳遞參數

若建立一個預存程序

create proc spGetCustomers @Country nvarchar(50),@City nvarchar(50), @Region nvarchar(50)=NULL
as
IF @Region IS NULL
    select * from Customers where Country=@Country and City=@City
ELSE
    select * from Customers where Country=@Country and City=@City and Region=@Region

透過 RS 呼叫時,若選擇的是預存程序

image

image

如同 .NET 程式碼

Dim cmd As New SqlCommand("spGetCustomers", cnn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@City", "Berlin"))
cmd.Parameters.Add(New SqlParameter("@Country", "Germany"))
cmd.Parameters.Add(New SqlParameter("@Region", DBNull.Value))

ADO.NET 實際產生的 T-SQL 語法如下:

exec spGetCustomers @City=N’Berlin’,@Country=N’Germany’,@Region=NULL

這是 By Name 傳遞參數,不必管參數建立的順序。

 

 

若不想要使用 @Region 參數,則查詢類型可以使用文字,但要小心參數的寫法,若查詢語法只是如下

spGetCustomers @City,@Country

設計畫面如下:

image

則如同 .NET 程式

Dim cmd As New SqlCommand("exec spGetCustomers @City, @Country", cnn)
cmd.Parameters.Add(New SqlParameter("@City", "Berlin"))
cmd.Parameters.Add(New SqlParameter("@Country", "Germany"))

ADO.NET 組出來的 T-SQL 如下:

exec sp_executesql N’exec spGetCustomers @City, @Country’,N’@Country nvarchar(7),@City nvarchar(6)’,@Country=N’Germany’,@City=N’Berlin’

這是 By Position 傳遞,則自己寫的參數順序就需要小心了。上述語法就會因為實際參數順序錯誤而沒有回傳結果,甚至是欄位型態錯誤

 

 

若要 By Name 傳遞,則查詢語法需帶參數名稱:

spGetCustomers @City=@City, @Country=@Country

設計畫面如下:

image

實際傳遞到 SQL Server 的 T-SQL 語法如下:

exec sp_executesql N’spGetCustomers @City=@City, @Country=@Country’,N’@Country nvarchar(3),@City nvarchar(7)’,@Country=N’Germany’,@City=N’Berlin’

這是 By Name 傳遞,就沒有參數順序的問題。

設計如此的查詢語法在取得欄位定義,執行報表都可以,但在設計工具 UI 的解析會有點問題。

3 Comments

  1. Terry
    Posted 2011 年 05 月 20 日 at 07:15:29 | Permalink | 回覆

    不好意思,我看了很久發現最後一個範例傳遞的TSQL語法,參數的順序應該沒關係才對,所以是不是Call By Name,還是我會錯意了。

    exec sp_executesql N’spGetCustomers @City=@City, @Country=@Country’,N’@Country nvarchar(3),@City nvarchar(7)’,@Country=N’Germany’,@City=N’Berlin’

  2. Posted 2011 年 05 月 20 日 at 07:29:09 | Permalink | 回覆

    Sorry,最後一段打錯了,你是對的。
    我本來就是要強調最後傳遞參數的方式是 by name,與順序無關 :)。
    這是寫這篇 blog 的主因,卻在結論時寫錯:(…非常感謝你的細心。

  3. Terry
    Posted 2011 年 05 月 20 日 at 08:57:34 | Permalink | 回覆

    不好意思,因為看起來怪怪的,所以跟您求證一下,謝謝老師的回覆。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: