如何创建一个带有值和逗号分隔列表的存储过程

问题描述

我需要有关创建存储过程的帮助，该存储过程带有参数@roleID和逗号分隔列表@UserIDs。



我需要更新或插入一个包含2列userID和RoleID的表。

从逗号分隔列表中我需要拆分字符串并获取每个用户ID然后在更新或插入语句中使用它，我将roleID作为@roleID分配给列表中的每个用户。我应该为列表中的所有用户ID做。



请帮我构建这样的SP。



先谢谢。

Hi,

I need help on creating a stored procedure which takes a parameter @roleID and a comma separated list @UserIDs.

I need to update or insert into a table which has 2 columns userID and RoleID.
From the comma separated list I need to split the string and get each userID then use it to in an update or Insert Statement where I assign roleID as @roleID to each user in the list. And this I should do for all the userIDs in the list.

please help me construct such an SP.

Thanks in Advance.

推荐答案

delete from UserRole where RoleID = @RoleID
--and UserID=(Select tbl.id from dbo.CSVToTable(@UserIDs) tbl)

不会是WOrk：它返回一组结果，而不是一个结果。

试试这个：

Won't WOrk: it returns a set of result, not a single result.
Try this:

delete from UserRole where RoleID = @RoleID
AND UserID IN (Select id from dbo.CSVToTable(@UserIDs))

然后您的INSERT：

Then for your INSERT:

INSERT INTO UserRole(UserID, RoleId) SELECT id, @RoleID FROM dbo.CSVToTable(@UserIDs)

[/ EDIT]

CREATE PROCEDURE [sp_Name]
@RoleID INT=0,
@UserID varchar(max)='',
@strQuery nvarchar(max)=''
AS
	BEGIN
		select @strQuery='UPDATE TableName SET [RoleID]='+convert(varchar,@RoleID)+' where UserID IN ('+@UserID+')'
		exec (@strQuery)
	END
GO

这篇关于如何创建一个带有值和逗号分隔列表的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！