将一对字符串转换成字典(临时表)
但在自定义函数中使用 RAISERROR抛出错误时会有错(存储过程中可以),望赐教
使用方法:
select * from f_dic('aa,bfeab,cc,dxd,eeeee', '1,2,4,1,5')
结果
aa 1
bfeab 2
cc 4
dxd 1
eeeee 5
CREATE
FUNCTION
[
dbo
]
.
[
f_dic
]
(
@keys VARCHAR ( 8000 ),
@values VARCHAR ( 8000 )
)
RETURNS @t TABLE ( [ key ] VARCHAR ( 20 ),value VARCHAR ( 20 ))
AS
BEGIN
-- IF LEN(@keys)-LEN(REPLACE(@keys,',',''))<>LEN(@values)-LEN(REPLACE(@values,',',''))
-- BEGIN
-- RAISERROR (N'参数@keys的长度与@values的长度不一至',
-- 10,1);
-- END
WHILE ( CHARINDEX ( ' , ' , @keys ) <> 0 )
BEGIN
INSERT @t ( [ key ] ,value) VALUES ( SUBSTRING ( @keys , 1 , CHARINDEX ( ' , ' , @keys ) - 1 ), SUBSTRING ( @values , 1 , CHARINDEX ( ' , ' , @values ) - 1 ))
SET @keys = STUFF ( @keys , 1 , CHARINDEX ( ' , ' , @keys ), '' )
SET @values = STUFF ( @values , 1 , CHARINDEX ( ' , ' , @values ), '' )
END
INSERT @t ( [ key ] ,value) VALUES ( @keys , @values )
RETURN
END
(
@keys VARCHAR ( 8000 ),
@values VARCHAR ( 8000 )
)
RETURNS @t TABLE ( [ key ] VARCHAR ( 20 ),value VARCHAR ( 20 ))
AS
BEGIN
-- IF LEN(@keys)-LEN(REPLACE(@keys,',',''))<>LEN(@values)-LEN(REPLACE(@values,',',''))
-- BEGIN
-- RAISERROR (N'参数@keys的长度与@values的长度不一至',
-- 10,1);
-- END
WHILE ( CHARINDEX ( ' , ' , @keys ) <> 0 )
BEGIN
INSERT @t ( [ key ] ,value) VALUES ( SUBSTRING ( @keys , 1 , CHARINDEX ( ' , ' , @keys ) - 1 ), SUBSTRING ( @values , 1 , CHARINDEX ( ' , ' , @values ) - 1 ))
SET @keys = STUFF ( @keys , 1 , CHARINDEX ( ' , ' , @keys ), '' )
SET @values = STUFF ( @values , 1 , CHARINDEX ( ' , ' , @values ), '' )
END
INSERT @t ( [ key ] ,value) VALUES ( @keys , @values )
RETURN
END