通过字符串列表存储过程 [英] Pass List of strings to a stored procedure
问题描述
使用,SQL Server 2012中,我想创建一个存储过程,传递字符串和检查的列表是每个entry.Iv添加列表中的一个逗号分隔字符串'UserGroupsAllowedToViewMap。这是工作的一个条目,但我需要检查它的多个条目。
Using, SQL Server 2012, I would like to create a stored procedure, that passes in a list of string and checks it for each entry.Iv added the list to one comma separated string 'UserGroupsAllowedToViewMap'. This was working for one entry but I need to check it for a number of entries.
public DataTable GetMapsWithWorkspaceForUserGroups(int workspaceID, string UserGroupsAllowedToViewMap)
{
DataTable mapDets = new DataTable();
SqlCommand oComm = new SqlCommand();
SqlParameter spParam_WrkSpaceId = new SqlParameter();
SqlParameter spParam_ViewMap = new SqlParameter();
SqlParameter[] spParams = new SqlParameter[2];
SqlDataAdapter daUserMaps = new SqlDataAdapter();
try
{
spParam_WrkSpaceId.ParameterName = "@workspaceID";
spParam_WrkSpaceId.Value = workspaceID;
spParams[0] = spParam_WrkSpaceId;
spParam_ViewMap.ParameterName = "@ViewMap";
spParam_ViewMap.Value = UserGroupsAllowedToViewMap;
spParams[1] = spParam_ViewMap;
oComm = CreateCommand("GetWorkspaceMapDetailsForUserByGroups", spParams, TypeOfConnectionString.GeoAppBuilder);
daUserMaps.SelectCommand = oComm;
daUserMaps.Fill(mapDets);
}
catch (Exception e)
{
throw (e);
}
finally
{
CloseConnection();
}
return mapDets;
}
USE [App]
GO
/****** Object: StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups] Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]
@workspaceID int,
@viewMap nvarchar(256)
AS
SELECT
m.*
FROM
GeoAppMapDef m
WHERE
m.workspaceID = @workspaceID
and m.IsDeleted = 0
and m.ViewMap = @viewMap
我不能确定如何通过SQL字符串迭代。香港专业教育学院看着传递名单,LT;>到SQL存储过程和 C# SQL服务器 - 传递一个列表到存储过程但仍然毫无收获。任何帮助表示赞赏。
I am unsure how to iterate through the string in SQL. Ive looked at Passing List<> to SQL Stored Procedure and C# SQL Server - Passing a list to a stored procedure but still none the wiser. any help appreciated.
推荐答案
使用XML的逗号分隔值转换为表。使用此更新程序。
Convert the comma seperated value to table using the XML. Use this updated procedure.
USE [App]
GO
/****** Object: StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]
Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]
@workspaceID int,
@viewMap nvarchar(256)
AS
SELECT
m.*
FROM
GeoAppMapDef m
WHERE
m.workspaceID = @workspaceID
and m.IsDeleted = 0
and m.ViewMap IN
(
SELECT
Split.a.value('.', 'VARCHAR(100)') AS CVS
FROM
(
SELECT CAST ('<M>' + REPLACE(@viewMap, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
)
这篇关于通过字符串列表存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!