方案一：

Create FUNCTION [dbo].Get_Contract_StyleNo(@AutoInc bigint)

RETURNS VARCHAR(Max)

AS

BEGIN

ｄｅｃｌａｒｅ @Content NVARCHAR(MAX)

SET @Content = ”

ｓｅｌｅｃｔ @Content = case when @Content=” then ” else @Content + ‘,’ end + GoodsNo

FROM (ｓｅｌｅｃｔ distinct GoodsNo from EXP_Contract_Clothing_Goods where MainID=@AutoInc) m

RETURN @Content

END

GO

构造标量函数

使用xml

构造通用存储过程

方案三：

——默认条件，MainID=AutoInc

alter procedure [dbo].[SP_SumStr]

(

@MTable Varchar(100)–主表

,@ZTable Varchar(100)–子表

,@MColumn Varchar(100)–主表更新字段

,@ZColumn Varchar(100)–子表拼接字段

,@ConditionSql Varchar(500)–传入的条件语句

)

as

set nocount on

begin

ｄｅｃｌａｒｅ @strSql varchar(3000)

ｓｅｌｅｃｔ top 1 AutoInc as MainID, Convert(nvarchar(500),CnName) as GoodsNo

into #result

from SYS_Staff where 1<0

set @strSql = ‘ｉｎｓｅｒｔ into #result ｓｅｌｅｃｔ distinct MainID,’+ @ZColumn +’ from ‘+@ZTable+’ ‘ + @ConditionSql

ｅｘｅｃ(@strSql)

ｄｅｃｌａｒｅ @Content NVARCHAR(MAX)

SET @Content = ”

ｓｅｌｅｃｔ @Content = case when @Content=” then ” else @Content + ‘,’ end + GoodsNo FROM #result

set @strSql = ‘ｕｐｄａｔｅ a set a.’+@MColumn+’=’+@Content+’ from ‘+ @MTable +’ a left join ‘+@ZTable+’ b on a.AutoInc=b.MainID ‘ + @ConditionSql

ｅｘｅｃ(@strSql)

—-ｓｅｌｅｃｔ @Content

end

set nocount off