create table jhmStyleSendOffDetail(sendoff_num varchar(16), sample_no varchar(16), sample_no_seq varchar(16), sample_type varchar(16), barcode_desc varchar(16))
insert jhmStyleSendOffDetail select '200803200001', 'JS08K00634', '01', '002', '108009833'
union all select '200803200001', 'JS08K00634', '01', '002', '108009835'
union all select '200803200001', 'JS08K00634', '01', '002', '108009834'
union all select '200803200001', 'JS08K00634', '01', '002', '108009838'
union all select '200803200002', 'JS08K00634', '01', '002', '108009833'
union all select '200803200002', 'JS08K00634', '01', '002', '108009835'
union all select '200803200002', 'JS08K00634', '01', '002', '108009834'
union all select '200803200002', 'JS08K00634', '01', '002', '108009838'GO
CREATE FUNCTION dbo.getbarcode(@MM VARCHAR(16),@PM VARCHAR(16),@AM VARCHAR(2),@Deli varchar(1)=',')
RETURNS varchar(1000) AS
BEGIN
if @deli=''
set @deli=' '
declare @result varchar(1000) SELECT @result=isnull(@result+@deli, '')+barcode_desc
FROM jhmStyleSendOffDetail
WHERE sendoff_num =@MM and sample_no=@PM and sample_no_seq=@AM return (@result)
end
go
select dbo.getbarcode('200803200001','JS08K00634','01',',') as barcode_desc
/*
barcode_desc
---------------------------------------------------
108009833,108009835,108009834,108009838(1 row(s) affected)
*/drop function dbo.getbarcode
drop table jhmStyleSendOffDetail
insert jhmStyleSendOffDetail select '200803200001', 'JS08K00634', '01', '002', '108009833'
union all select '200803200001', 'JS08K00634', '01', '002', '108009835'
union all select '200803200001', 'JS08K00634', '01', '002', '108009834'
union all select '200803200001', 'JS08K00634', '01', '002', '108009838'
union all select '200803200002', 'JS08K00634', '01', '002', '108009833'
union all select '200803200002', 'JS08K00634', '01', '002', '108009835'
union all select '200803200002', 'JS08K00634', '01', '002', '108009834'
union all select '200803200002', 'JS08K00634', '01', '002', '108009838'GO
CREATE FUNCTION dbo.getbarcode(@MM VARCHAR(16),@PM VARCHAR(16),@AM VARCHAR(2),@Deli varchar(1)=',')
RETURNS varchar(1000) AS
BEGIN
if @deli=''
set @deli=' '
declare @result varchar(1000) SELECT @result=isnull(@result+@deli, '')+barcode_desc
FROM jhmStyleSendOffDetail
WHERE sendoff_num =@MM and sample_no=@PM and sample_no_seq=@AM return (@result)
end
go
select dbo.getbarcode('200803200001','JS08K00634','01',',') as barcode_desc
/*
barcode_desc
---------------------------------------------------
108009833,108009835,108009834,108009838(1 row(s) affected)
*/drop function dbo.getbarcode
drop table jhmStyleSendOffDetail
将参数长度改到16就OK了,建议你用4楼的代码,不用游标,代码简洁,效率高高,,,
--函数改一下CREATE FUNCTION dbo.getbarcode(@MM varchar(50),@PM VARCHAR(50),@AM VARCHAR(10),@Deli varchar(1)=',')
RETURNS varchar(1000)
AS
BEGIN
declare @result varchar(1000)
SELECT DISTINCT @result=isnull(@result+@Deli,'')+barcode_desc
FROM jhmStyleSendOffDetail WHERE sendoff_num =@MM and sample_no=@PM and sample_no_seq=@AM
return @result
end
--调用:select dbo.getbarcode('200803200001','JS08K00634','01',',') as barcode_desc
dobear_0922兄写的自定义函数非常好用
pt1314917兄写的好像不行哦
谢谢两位拉!!!