以下为删除记录的存储过程,此删除为逻辑删除,只是将记录的状态字段state改为'2'了,正常为'1'
/*
*****************************************************************************
*根据ID删除黑名单表中记录信息
*****************************************************************************
*/
CREATE PROCEDURE [dbo].[P_DelBlackListByID_sp]
@IDList nvarchar(2000)
AS
DECLARE @Len int
DECLARE @id varchar(30)
SELECT @Len = DATALENGTH(@idList)
WHILE @Len> 0
BEGIN
EXECUTE @Len = GetFirstWordWithSeparator_sp @idList OUTPUT, @id OUTPUT,','
IF @Len > 0
BEGIN
update BlackList_tb set [state]='2' WHERE BlackListID=cast(@id as int)
END
End
以上存储过程调用了下面的存储过程
CREATE PROCEDURE [dbo].[GetFirstWordWithSeparator_sp]
@SourceString nvarchar(4000) = NULL OUTPUT,
@FirstWord nvarchar(4000) = NULL OUTPUT,
@Separator nvarchar(20)
AS
SET NOCOUNT ON
DECLARE @Oldword nvarchar(4000)
DECLARE @Length int
DECLARE @CommaLocation int
SELECT @Oldword = @SourceString
IF NOT @Oldword IS NULL
BEGIN
SELECT @CommaLocation = CHARINDEX(@Separator,@Oldword)
SELECT @Length = DATALENGTH(@Oldword)
IF @CommaLocation = 0
BEGIN
SELECT @FirstWord = @Oldword
SELECT @SourceString = NULL
RETURN @Length
END
SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)
SELECT @SourceString = SUBSTRING(@Oldword, @CommaLocation + LEN(@Separator), @Length - @CommaLocation)
RETURN @Length - @CommaLocation
END
RETURN 0
这样就可以一次性向数据库传递一个字符串id集合对数据进行操作了,这样做可以不用in关键字。
PS:我的淘宝店铺新开业,经营各种桌游,棋牌,希望大伙儿能来看看!http://201314yes.taobao.com/