CREATE FUNCTION dbo.f_rpttest(@rptid char)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + cfieldname FROM sys_rptfield WHERE rptid=@rptid
RETURN STUFF(@str, 1, 1, '')
END
GO SELECt rptid,cfieldname=dbo.f_rpttest(rptid) FROM sys_rptfield GROUP BY rptid
drop function dbo.f_rpttest
go
为什么我的的出的结果中cfieldname只是null啊
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + cfieldname FROM sys_rptfield WHERE rptid=@rptid
RETURN STUFF(@str, 1, 1, '')
END
GO SELECt rptid,cfieldname=dbo.f_rpttest(rptid) FROM sys_rptfield GROUP BY rptid
drop function dbo.f_rpttest
go
为什么我的的出的结果中cfieldname只是null啊
R10030001 日期
R10030001 船名/航次
R10030001 货物名称
R10030001 数量
我按照上面的语句得出cfieldname怎么是NULL啊?
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SELECT @str = @str + ',' + cfieldname FROM sys_rptfield WHERE rptid=@rptid
RETURN STUFF(@str, 1, 1, '')
END
GO (@rptid varchar(20)) 类型
WHERE rptid=RTRIM(@rptid)照理来说有空格也不会是NULL啊,改成这样吧
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-12 09:56:46
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([rptid] varchar(9),[cfieldname] varchar(9))
insert [tb]
select 'R10030001','日期' union all
select 'R10030001','船名/航次' union all
select 'R10030001','货物名称' union all
select 'R10030001','数量'
--------------开始查询--------------------------
CREATE FUNCTION dbo.f_rpttest(@rptid varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + isnull(cfieldname,'') FROM tb WHERE rptid=@rptid
RETURN STUFF(@str, 1, 1, '')
END
GO SELECT rptid,cfieldname=dbo.f_rpttest(rptid) FROM tb GROUP BY rptid
drop function dbo.f_rpttest
go----------------结果----------------------------
/* rptid cfieldname
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
R10030001 日期,船名/航次,货物名称,数量(所影响的行数为 1 行)
*/
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = '' -- you lost this
SELECT @str = @str + ',' + cfieldname FROM sys_rptfield WHERE rptid=@rptid
RETURN STUFF(@str, 1, 1, '')
END
GO
RETURNS varchar(1000)
as
begin
RETURN nullif('a',@rptid)
end
select dbo.f_rpttest('a')-----------------------------
NULL(1 行受影响)