首页 文章

将字符串列表传递给存储过程[重复]

提问于
浏览 次
-1

这个问题在这里已有答案：

使用，SQL Server 2012，我想创建一个存储过程，传入一个字符串列表并检查每个条目.Iv将列表添加到一个逗号分隔字符串'UserGroupsAllowedToViewMap' . 这是为一个条目工作，但我需要检查它的一些条目 .

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中迭代字符串 . 我看了Passing List<> to SQL Stored Procedure和C# SQL Server - Passing a list to a stored procedure但仍然没有更聪明 . 任何帮助赞赏 .

c# sql stored-procedures sql-server-2012

2 回答

  • 4

    使用XML将逗号分隔值转换为表 . 使用此更新程序 .

    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)
)
    回复于
  • 1

    这实际上是您发布的链接的副本 . 不是尝试解析值列表，而是传递表值参数 .

    首先在数据库中创建参数的类型（仅一次） .

    CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] int NULL
);

    然后创建一个接受此参数的过程：

    CREATE PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]
    @workspaceID int,
    @groupIds IdList READONLY
AS
BEGIN
SELECT 
  m.*
FROM GeoAppMapDef m 
    inner join @groupIds on m.ViewMap=@groupIds.Id
WHERE
    m.workspaceID = @workspaceID
    and m.IsDeleted = 0
END

    在客户端，使用名为 Id 的单个int类型列创建一个DataTable，用您想要的ID填充它，然后将其用作 @groupIds 参数的值

    var table = new DataTable();
table.Columns.Add("Id", typeof(int));

for (int i = 0; i < 10; i++)
    table.Rows.Add(i);

var pList = new SqlParameter("@groupIds", SqlDbType.Structured);
pList.TypeName = "dbo.IdList";
pList.Value = table;

    我通过一些修改从复制的问题中复制了这个 .

    回复于

相关问题