sql - 如何在存储过程中将EventAccessIds = 1 + 4 + 8分解为EventId = 1，EventId = 4和EventId = 8？

用户可以访问特定事件。我在用户中有一行名为eventaccessids。我想拥有所有用户可以通过某种方式访问的ID。如果用户a应该访问事件1、4和8，那么eventaccessids应该是1+4+8或类似的值。
然后我想取出这些id并使用select语句填充不同区域（dropdownlists、gridview等）的数据。实际上，它将为用户a填充事件id 1、4和8的数据。
问题是，我对数据库和sql还不熟悉，所以我不太确定如何编写所有这些代码。我正在使用存储过程。我在想也许可以用这样的方式将EventAccessID发送到事件：

WITH first AS
    (
   SELECT EventAccessIds
    FROM Users
    WHERE UserName = @UserName
    ), second AS
    (
    SELECT *
    FROM Event
    WHERE EventId = EventAccessIds
    )

但是如何将eventAccessID=1+4+8分解为eventID=1、eventID=4和eventID=8？
编辑
SpgetAllEvents公司

USE [Events2]
GO
/****** Object:  StoredProcedure [dbo].[spGetAllEvents]    Script Date: 11/19/2015 9:03:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetAllEvents]
    @UserName nVarChar(80)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
WITH first
AS
(
    SELECT EventAccessIds
    FROM Users
    WHERE UserName = @UserName
), second AS
(
    SELECT e.* FROM [Event] as e
    WHERE e.EventId in
    (
        SELECT convert(int, s.Data)
        FROM [first] as f
        CROSS APPLY
        (SELECT * FROM dbo.Split(f.EventAccessIds, '+')) as s
    )
)
END

用户（第一张图片）和事件（第二张图片）

最佳答案

首先，由于SQL Server没有EventAccessIds功能，您必须使用自己的函数拆分Split。
其中一个很好的例子发表在Ole Michelsen's blog上。

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

然后，可以像使用int数组一样使用拆分的表：

WITH first AS
(
    SELECT EventAccessIds
    FROM Users
    WHERE UserName = @UserName
), second AS
(
    SELECT *
    FROM Event
    WHERE EventId IN (SELECT CONVERT(int, Data) FROM dbo.Split(EventAccessIds, '+')
)

更新
由于您的[Second]查询似乎引用了在[First]查询中选择的EventAccessIds，所以应该如下所示：

WITH first
AS
(
    SELECT EventAccessIds
    FROM Users
    WHERE UserName = @UserName
), second AS
(
    SELECT e.* FROM [Event] as e
    WHERE e.EventId in
    (
        SELECT convert(int, s.Data)
        FROM [first] as f
        CROSS APPLY
        (SELECT * FROM dbo.Split(f.EventAccessIds, '+')) as s
    )
)