传入某种规则拼接字符串获得数组（表）
/*
功能说明：传入字符串跟分割符('''SGHE00000003'',''SGHE00000004'',''SGHE00000005''')，返回一个Table
*/
ALTER FUNCTION [dbo].[fnSys_SplitString]
(
---字符串分割
@Str VARCHAR(MAX), --传入的字符串
@SeprateStr VARCHAR(10)--分隔符
)
RETURNS @temp TABLE(Code VARCHAR(100)) --返回一个Table
AS
BEGIN
DECLARE @i INT
SET @Str=REPLACE(@str,'''','')
SET @Str =RTRIM(LTRIM(@Str ))
SET @i=CHARINDEX(@SeprateStr,@Str )
WHILE @i>=1
BEGIN
INSERT @temp VALUES(LEFT(@Str ,@i-1))
SET @Str =SUBSTRING(@Str ,@i+1,LEN(@Str )-@i)
SET @i=CHARINDEX(@SeprateStr,@Str )
END
IF @Str <>''
INSERT @temp VALUES(@Str )
RETURN
END
使用此函数拓展方法调用
CREATE PROC WriteUnionIDByBatch
@UnionIDs VARCHAR(MAX),@OpenIDs VARCHAR(MAX),@CompanyID VARCHAR(50),@AccountID VARCHAR(50)
AS
BEGIN
DECLARE @unionID VARCHAR(50),@openid VARCHAR(50)
CREATE TABLE #OpenIDTable
(
openid VARCHAR(50),
unionid VARCHAR(50)
)
INSERT #OpenIDTable
SELECT oldopenid,newopenid FROM dbo.[fnSys_SplitString_ChangeOpenID](@OpenIDs,@UnionIDs,',')
DECLARE mycursor CURSOR FOR
SELECT openid,unionid FROM #OpenIDTable
OPEN mycursor
FETCH NEXT FROM mycursor INTO @openid,@unionID
WHILE @@FETCH_STATUS = 0
BEGIN
IF(ISNULL(@unionID,''))>''
BEGIN
UPDATE dbo.EWMS_Web_Member SET UnionID =@unionID WHERE CompanyID = @CompanyID AND AccountID = @AccountID
END
FETCH NEXT FROM mycursor INTO @openid,@unionID
END
CLOSE mycursor
DEALLOCATE mycursor
END