如何创建一个带有值和逗号分隔列表的存储过程 [英] How to create a stored proc which takes a value and a comma separated list

问题描述

我需要有关创建存储过程的帮助，该存储过程带有参数@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.

推荐答案

看看这个：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ] - 它不能完全按照你的意愿行事，但它会告诉你如何处理逗号分隔值的驴工作。你应该可以从那里处理它。







好吧，你还没有完全掌握这个，对吗？ ：笑：

Have a look at this: Using comma separated value parameter strings in SQL IN clauses[^] - it doesn't do exactly what you want, but it shows you how to handle the "donkey work" of the comma separated values. You should be able to handle it from there.



OK, you haven't quite got the hang of this, have you? :laugh:

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]

使用in子句为

通过此过程，您可以将相同的 roleID 分配给多个逗号分隔 UserID

Use in clause for that
By this procedure you can assign same roleID to multiple comma separated UserID

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

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