方案一：

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

RETURNS VARCHAR(Max)

AS

BEGIN

DECLARE @Content NVARCHAR(MAX)

SET @Content = ”

SELECT @Content = case when @Content=” then ” else @Content + ‘,’ end + GoodsNo

FROM (select 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

declare @strSql varchar(3000)

select top 1 AutoInc as MainID, Convert(nvarchar(500),CnName) as GoodsNo

into #result

from SYS_Staff where 1

set @strSql = ‘insert into #result select distinct MainID,’+ @ZColumn +’ from ‘+@ZTable+’ ‘ + @ConditionSql

exec(@strSql)

DECLARE @Content NVARCHAR(MAX)

SET @Content = ”

SELECT @Content = case when @Content=” then ” else @Content + ‘,’ end + GoodsNo FROM #result

set @strSql = ‘Update a set a.’+@MColumn+’=’+@Content+’ from ‘+ @MTable +’ a left join ‘+@ZTable+’ b on a.AutoInc=b.MainID ‘ + @ConditionSql

exec(@strSql)

—-SELECT @Content

end

set nocount off