sql查询拼接字符串(sql字符串拼接方法)
sql查询拼接字符串(sql字符串拼接方法)FROM (select distinct GoodsNo from EXP_Contract_Clothing_Goods where MainID=@AutoInc) mSELECT @Content = case when @Content='' then '' else @Content ' ' end GoodsNoBEGINDECLARE @Content NVARCHAR(MAX)SET @Content = ''
方案一:
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<0
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