将字符串列表传递给存储过程 [英] Pass List of strings to a stored procedure

问题描述

使用 SQL Server 2012，我想创建一个存储过程，它传入一个字符串列表并检查每个条目.我将该列表添加到一个逗号分隔的字符串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 中的字符串.我看过传递列表<>到 SQL 存储过程 和 C#SQL Server - 将列表传递给存储过程，但仍然不明智.任何帮助表示赞赏.

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屋！