新建了一个函数，将交叉表中的数据读出！老是错误！请高手看看!

[Chimae]

具体查看http://expert.csdn.net/Expert/topic/2530/2530994.xml?temp=.8444025
我想把Exec（@sql）换成函数实现！高手指点！
CREATE FUNCTION fcMCGroundFactorIndex ()
RETURNS @tmpTable TABLE
AS
BEGIN
declare @sql varchar(8000)
set @sql = 'select viewMCExampleFactor.iFactorClassSn,viewMCGroundFactorDescribe.iIndex AS ''主单'''
select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar) +'''
then isnull(viewMCExampleFactor.iIndex,0) else 0 end) ['+ '案例'+cast(sAliasName as varchar)+']'
from (select distinct sExampleNo,sAliasName from viewMCExampleFactor) as a
INSERT INTO @tmpTable @sql
END
RETURN

[pengdali]

函数不可以用exec这是sqlserver的约束

[pengdali]

create proc fcMCGroundFactorIndex
AS
BEGIN
declare @sql varchar(8000)
set @sql = 'select viewMCExampleFactor.iFactorClassSn,viewMCGroundFactorDescribe.iIndex [主单]'
select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10))+''' then isnull(viewMCExampleFactor.iIndex,0) else 0 end) ['+ '案例'+cast(sAliasName as varchar(10))+']'
from (select distinct sExampleNo,sAliasName from viewMCExampleFactor) as a
exec(@sql)
END

[pengdali]

1、
不要懒varchar后加长度

2、
@sql是有长度限制的,不要加多余的空格、回车



create proc fcMCGroundFactorIndex
AS
BEGIN
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10))+''' then isnull(viewMCExampleFactor.iIndex,0) else 0 end) ['+ '案例'+cast(sAliasName as varchar(10))+']'
from (select distinct sExampleNo,sAliasName from viewMCExampleFactor) as a
exec('select viewMCExampleFactor.iFactorClassSn,viewMCGroundFactorDescribe.iIndex [主单] '+@sql+' from 你的表')
END

[Chimae]

pengdali(大力 V3.0)，我现在用函数返回表的形式来返回上面的记录集该如何实现！因为我的iFactorClassSn还有对应的关联表，我要将对应的关联表数据也要读到该记录集中来！

[Chimae]

呵呵，没刷新，我试试！谢谢！

[Chimae]

pengdali(大力 V3.0)，谢谢你的提点，我用函数能实现返回上面的记录集吗？

[txlicenhe]

函数中不能用动态SQL语句，
非确定性函数

@@ERROR
FORMATMESSAGE
NEWID
IDENTITY
GETANSINULL
PATINDEX
@@ROWCOUNT
GETDATE
PERMISSIONS
@@TRANCOUNT
GetUTCDate
SESSION_USER
APP_NAME
HOST_ID
STATS_DATE
CHARINDEX
HOST_NAME
SYSTEM_USER
CURRENT_TIMESTAMP
IDENT_INCR
TEXTPTR
CURRENT_USER
IDENT_SEED
TEXTVALID
DATENAME
IDENTITY
USER_NAME
以上这些函数都不能在自定义函数中使用

[aierong]

函数不可以动态运行SQL语句

你改为过程把

EXEC(@SQL)
或者
declare @sql nvarchar(4000)
execute sp_executesql @sql

[Chimae]

谢谢大家！结分！

[pengdali]

create proc fcMCGroundFactorIndex
AS
BEGIN
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',sum(case sExampleNo when '''+ cast(sExampleNo as varchar(10))+''' then isnull(viewMCExampleFactor.iIndex,0) else 0 end) ['+ '案例'+cast(sAliasName as varchar(10))+']'
from (select distinct sExampleNo,sAliasName from viewMCExampleFactor) as a
exec('select viewMCExampleFactor.iFactorClassSn,viewMCGroundFactorDescribe.iIndex [主单] '+@sql+' into ##全局临时表 from 你的表')
END
go

......