sqlserver 2008 存储过程调用存储过程或方法

函数：拆分字符串，并返回一个table

CREATE FUNCTION [dbo].[f_splitSTR](
@s varchar(max), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col1 varchar(100))
AS
BEGIN
DECLARE
@splitlen int

SET @splitlen=LEN(@split+'a')-2

WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')

END
INSERT @re VALUES(@s)
RETURN
END

 

调用上面函数的存储过程：

CREATE PROCEDURE [dbo].[spAddFileCate]
@Fileid bigint,@cateid int,@othercateid nvarchar(1000)
as
begin
insert into FILE_CATEGORY_MAPPING(FILEID,CATEGORY_ID)values(@Fileid,@cateid)
if(@othercateid<>'')
begin
insert into FILE_CATEGORY_MAPPING(CATEGORY_ID,FILEID) select *, @Fileid from f_splitSTR(@othercateid,';')
end

end

GO

调用上面存储过程的存储过程

CREATE PROCEDURE spGetFileCate
@FileID bigint
AS
BEGIN
select * from FILE_CATEGORY_MAPPING where FILEID=@FileID;

exec [dbo].[spAddFileCate] @FileID,'4','5;6;7'
END