使用表值参数时如何将多个参数一起传递给存储过程 [英] while using table valued parameter how to pass multiple parameter together to stored prcocedure
问题描述
我有一个像这样的表值参数
I have a table-valued parameter like this
CREATE TYPE dbo.Loc AS TABLE(Lo integer);
CREATE TYPE dbo.Loc AS TABLE(Lo integer);
我的存储过程如下所示:
My stored procedure looks like this:
ALTER PROCEDURE [dbo].[T_TransactionSummary]
@startDate datetime,
@endDate datetime,
@locations dbo.Loc readonly
..........................
...........................
WHERE (Transaction_tbl.dtime BETWEEN @fromDate AND @toDate)
AND (Location_tbl.Locid IN (select Lo from @locations))
我有一个包含多个项目的列表框.我可以从列表框中选择多个项目.如何将多个 Locationid 传递给我的存储过程
I have a listbox that contains multiple items. I can select multiple items from my listbox. How can I pass multiple Locationid to my stored procedure
cnt = LSTlocations.SelectedItems.Count
If cnt > 0 Then
For i = 0 To cnt - 1
Dim locationanme As String = LSTlocations.SelectedItems(i).ToString
locid = RecordID("Locid", "Location_tbl", "LocName", locationanme)
next
end if
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim cmd23 As New SqlCommand("IBS_TransactionSummary", con.connect)
cmd23.CommandType = CommandType.StoredProcedure
cmd23.Parameters.Add("@startDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value = startdate
cmd23.Parameters.Add("@endDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value = enddate
Dim tvp1 As SqlParameter =cmd23.Parameters.Add("@location", SqlDbType.Int).Value = locid
tvp1.SqlDbType = SqlDbType.Structured
tvp1.TypeName = "dbo.Loc"
da.SelectCommand = cmd23
da.Fill(ds)
但我收到错误..我正在 vb.net 中处理 Windows 窗体
but i am getting error..i am working on windows forms in vb.net
推荐答案
http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx(请参阅标题为将表值参数传递给存储过程的部分"").
There are some examples of how to do this at http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx (see the section titled "Passing a Table-Valued Parameter to a Stored Procedure ").
最简单的事情似乎是用用户选择的值填充 DataTable
并将其传递给 @locations
参数的存储过程.
The simplest thing would seem to be filling a DataTable
with the values the user selected and passing that to the stored procedure for the @locations
parameter.
也许是这样的(注意我没有安装 VB.NET,所以把它当作它应该如何工作的大纲,而不一定是可以立即工作的代码):
Perhaps something along the lines of (note I don't have VB.NET installed, so treat this as an outline of how it should work, not necessarily as code that will work straight away):
cnt = LSTlocations.SelectedItems.Count
' *** Set up the DataTable here: *** '
Dim locTable As New DataTable
locTable.Columns.Add("Lo", GetType(Integer))
If cnt > 0 Then
For i = 0 To cnt - 1
Dim locationanme As String = LSTlocations.SelectedItems(i).ToString
locid = RecordID("Locid", "Location_tbl", "LocName", locationanme)
' *** Add the ID to the table here: *** '
locTable.Rows.Add(locid)
next
end if
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim cmd23 As New SqlCommand("IBS_TransactionSummary", con.connect)
cmd23.CommandType = CommandType.StoredProcedure
cmd23.Parameters.Add("@startDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value = startdate
cmd23.Parameters.Add("@endDate", SqlDbType.NVarChar, 50, ParameterDirection.Input).Value = enddate
' *** Supply the DataTable as a parameter to the procedure here: *** '
Dim tvp1 As SqlParameter =cmd23.Parameters.AddWithValue("@location", locTable)
tvp1.SqlDbType = SqlDbType.Structured
tvp1.TypeName = "dbo.Loc"
da.SelectCommand = cmd23
da.Fill(ds)
这篇关于使用表值参数时如何将多个参数一起传递给存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!