1.存储过程
ALTER PROCEDURE dbo.GetFirstWord
@SourceString NVARCHAR(4000) = NULL OUTPUT,
@FirstWord NVARCHAR(4000) = NULL OUTPUT
AS
SET NOCOUNT ON
DECLARE @Oldword NVARCHAR(4000)
DECLARE @Length INT
DECLARE @CommaLocation INT
SELECT @Oldword = @SourceString
IF NOT @Oldword IS NULL
BEGIN
--get the first seperate chara location
SELECT @CommaLocation = CHARINDEX(',',@Oldword)
--get the length
SELECT @Length = DATALENGTH(@Oldword)
--Do Not Find Seperate,There is last Word
IF @CommaLocation = 0
BEGIN
SELECT @FirstWord = @Oldword
SELECT @SourceString = null
RETURN @Length
END
-- get the first word
SELECT @FirstWord = SUBSTRING(@Oldword,1,@CommaLocation-1)
--trim the first word and separate
SELECT @SourceString=SUBSTRING(@Oldword,@CommaLocation+1,@Length-@CommaLocation)
RETURN @Length - @CommaLocation
END
ELSE
SELECT @FirstWord = null
SELECT @Length = 0
RETURN 0
------------------------------------------------
ALTER PROCEDURE
dbo.DeleteUserdataByIdlist
@idlist nvarchar(1000)
AS
SET NOCOUNT ON
DECLARE @FirstWORD NVARCHAR(1000)
DECLARE @FIRSTID int
DECLARE @LENGTH int
SET @LENGTH = DATALENGTH(@idlist)
while @LENGTH > 0
BEGIN
EXEC @LENGTH = GetFirstWord @idlist OUTPUT ,@FirstWORD OUTPUT
if @LENGTH > 0
BEGIN
SELECT @FIRSTID = CONVERT(INT,@FirstWORD)
EXEC DeleteUserdatabyID @userid = @FIRSTID
END
END
RETURN 0
---------------------------------------------------------------------------------------------------------
2. 测试过程
在查询分析器中输入测试代码:
1. declare @tmp1 nvarchar(4000),@tmp2 nvarchar(4000),@rtn int
set
@tmp1 = 'a,b,cde'
SELECT
@rtn = DATALENGTH(@tmp1)
while
@rtn > 0
begin
exec @rtn = GetFirstWord @SourceString = @tmp1 output, @firstWord = @tmp2 output
print(@tmp2)
end
2.
exec DeleteUserdataByIdlist @idlist = '2,3,4,5,6' --同时删除多条记录
3. 输出结果
1.
a
b
cde
2.
删除了数据库中的记录