22,300
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(21),[defaultval] NVARCHAR(100))
Insert #T
select 1,N'a',null union all
select 2,N'b',null union all
select 3,N'c',null union all
select 4,N'd',null union all
select 5,N'f',null
Go
--测试数据结束
DECLARE @defaultvalue NVARCHAR(MAX)= '10,7,12,15,14';
;WITH cte
AS ( SELECT f1 ,
ROW_NUMBER() OVER ( ORDER BY f1 ) AS num
FROM dbo.f_splitstr(@defaultvalue, ',')
)
UPDATE #T
SET defaultval = cte.f1
FROM cte
WHERE cte.num = id
SELECT *
FROM #T
ALTER FUNCTION dbo.f_splitstr(@SourceSql NVARCHAR(MAX),@StrSeprate VARCHAR(100))
RETURNS @temp TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
SET @SourceSql=@SourceSql+@StrSeprate
WHILE(@SourceSql<>'')
BEGIN
SET @ch=LEFT(@SourceSql,CHARINDEX(@StrSeprate,@SourceSql,1)-1)
INSERT @temp VALUES(@ch)
SET @SourceSql=STUFF(@SourceSql,1,CHARINDEX(@StrSeprate,@SourceSql,1),'')
END
RETURN
END
GO