Sql存储过程传递数组

本文介绍了Sql存储过程传递数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一个datagridview，我需要显示频道的过滤搜索： 用户将指定：I have a datagridview where i need to display the filtered search of a channels:the user will specify:@ChannelType int,@ChannelStatusint,@govId int,@DistId int,@Budget money,@dt varchar(max),@minSqm int,@maxSqm int 我的问题在于规格列表（@dt） ：它是一个checkedlistbox，用户将选择规格，我必须找到他们拥有所选规格的通道。 所以我需要将它存储在一个数组中，以使其处于接近状态。 b $ b 所以我在c＃中的数组中列出了specs item id，并尝试传递给sql，但它无效。 我尝试过： 存储过程：My problem is with the specification list (@dt): it is a checkedlistbox where the user will choose specifications and i have to find the channels they have the selected specs.So i need to store this in an array to make the where in close condition.So i listed the specs item id in an array in c# and tried to passed to sql but it is not working.What I have tried:Stored procedure:USE [Tenant Management]GO/****** Object: StoredProcedure [dbo].[BuySearchEngine] Script Date: 8/31/2018 4:22:27 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[BuySearchEngine]@ChannelType int,@ChannelStatusint,@govId int,@DistId int,@Budget money,@dt varchar(max),@minSqm int,@maxSqm intASselect DISTINCT c.ID,c.Name,cc.[Channel Category], gv.GOVERNATOR + ' '+dst.District+' '+ct.City +' '+c.street As Location,c.Surface,c.Floor,c.[Selling Price],c.[Market price]from [dbo].[Channel_availableSpecs] aspinner join [dbo].[Channel] c on asp.Channel_Id = c.IDinner join [dbo].[Channel_Category] cc on c.[Channel Category ID] = cc.IDinner join [dbo].[Governator] gv on c.[Governator ID] = gv.IDinner join [dbo].[District] dst on c.[District ID] = dst.idinner join [dbo].[City] ct on c.[City ID] = ct.idwhere c.[Channel Type] =@ChannelType and c.[Channel Status]=@ChannelStatus and c.[Governator ID]=@govId and c.[District ID]=@DistId and c.[Selling Price] < @Budget and c.Surface >= @minSqm and c.Surface <= @maxSqm and asp.ChennelSpec_Id IN (SELECT asp.ChennelSpec_Id FROM Fn_split(@dt, ',')) C＃：C#: List<string> list = new List<string>(); private void checkedListBox1_ItemCheck(object sender, ItemCheckEventArgs e) { list.Add(checkedListBox1.SelectedValue.ToString()); }private void next_Click(object sender, EventArgs e) { int govId,distId,minSqm,maxSqm; Int32.TryParse(governator.SelectedValue?.ToString(),out govId); Int32.TryParse(district.SelectedValue?.ToString(),out distId); Int32.TryParse(sqm_from.Text,out minSqm); Int32.TryParse(sqm_to.Text,out maxSqm); if (rentbutton == 1) { rent_panel.Show(); } else { buy_search_panel.Show(); string[] Specs = new string[list.Count]; Utilities.BuySearch(Specs, govId, distId, minSqm,maxSqm,availability); list.Clear(); } availabilityFill(); } public static void BuySearch(Array array,int govId,int distId, int minSqm,int maxSqm,DataGridView dataGridView) { string specs = Get_comma_delimited_string(array); SqlConnection conn = new SqlConnection(ConnectionString); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "Execute BuySearchEngine @ChannelType, @ChannelStatus, @govId, @DistId, @Budget, @dt"; cmd.Parameters.Add("@ChannelType", SqlDbType.Int).Value = "1"; cmd.Parameters.Add("@ChannelStatus", SqlDbType.Int).Value ="5" ; cmd.Parameters.Add("@govId", SqlDbType.Int).Value = govId; cmd.Parameters.Add("@DistId", SqlDbType.Int).Value = distId; cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = specs; cmd.Parameters.Add("@minSqm", SqlDbType.Int).Value = minSqm; cmd.Parameters.Add("@maxSqm", SqlDbType.Int).Value = maxSqm; conn.Open(); DataTable dt = new DataTable(); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); dataGridView.DataSource = dt; conn.Close(); }推荐答案and asp.ChennelSpec_Id IN (SELECT asp.ChennelSpec_Id FROM Fn_split(@dt, ',')) 对于从 Fn_split ，您正在选择外表中的列。 您的代码相当于：For each item in the results returned from Fn_split, you're selecting the column from the outer table.Your code is equivalent to:and asp.ChennelSpec_Id = asp.ChennelSpec_Id对于 asp.ChennelSpec_Id 不是 NULL 。 您需要与您的 Fn_split 功能。确切的代码将取决于该函数返回的列的名称，但它看起来像： which is obviously going to be true for every row where asp.ChennelSpec_Id is not NULL.You need to compare against the values returned from your Fn_split function. The precise code will depend on the name of the column returned by that function, but it will look something like:and asp.ChennelSpec_Id IN (SELECT value FROM Fn_split(@dt, ',')) Fn_split返回什么？ 我不相信它会为每个项目返回asp.ChennelSpec_Id在@dt。 如果它返回一列，则可能需要在选择中稍作修改： SELECT whateverthenameofthereturncolumnis FROM Fn_split（ @dt，'，'）What does Fn_split return?I don't believe it returns asp.ChennelSpec_Id for each item in @dt.if it returns a column then might need to do a slight change in the select:SELECT whateverthenameofthereturncolumnis FROM Fn_split(@dt, ',') 这篇关于Sql存储过程传递数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！