SQL自定义函数返回值的问题

declare @str nvarchar(max)
declare @tem int


set @str=' type=1 or sort>20 '
set @str=N'select @tem_1=sum(id) from #order where ' +@str+ N' and Pid=20'
print @str
exec sp_executesql @str,N'@tem_1 int output',@tem_1=@tem output
select @tem


我想将以上代码写在function里面然后返回@tem
但是提示如下错误：
消息 557，级别 16，状态 2，第 1 行
只有函数和扩展存储过程才能从函数内部执行。

6 个解决方案

#1

函数里面不能exec 

#2

我写在存储过程后exec sp_executesql @str,N'@tem_1 int output',@tem_1=@tem output就会提示错误
我用print @str能正常显示SQL语句，但是执行就会出错

#3

declare @str nvarchar(max)
declare @tem int


set @str=' type=1 or sort>20 '
set @str=N'select @tem_1=sum(id) from #order where ' +@str+ N' and Pid=20'
print @str
exec sp_executesql @str,N'@tem_1 int output',@tem_1=@tem output

其实我主要是想要实现执行@str，然后将sum(id)赋值给@tem，好象在存储过程里面执行sp_executesql就有报错，说明：我在@str里面有用到别名

#4

引用 1 楼 maco_wang 的回复:

函数里面不能exec

在函数中是不能使用exec/sp_sqlexec/sp_executesql/print的.

create table [order](id int,type int,sort int,Pid int)

insert into [order](id,type,sort,Pid)
select 1,1,20,20  union all
select 2,1,20,20  union all
select 3,1,20,20  union all
select 4,1,20,20  union all
select 5,0,20,20  union all
select 6,0,21,20  

select * from [order]
go


create procedure usp_getvalue
(
	@value int output
)
as
begin
	declare @str nvarchar(max)
	declare @tem int

	set @str=' type=1 or sort>20 '
	set @str=N'select @tem_1=sum(id) from [order] where ' +@str+ N' and Pid=20'
	print @str
	exec sp_executesql @str,N'@tem_1 int output',@tem_1=@tem output
	set @str=' type=1 or sort>20 '
	set @value= @tem
end

go
declare @value int=0
exec usp_getvalue @value output
select @value 

#5

declare @Years nvarchar(20)
declare @Months nvarchar(10)
declare @Sort int
declare @str nvarchar(2000)
declare @t varchar(5)
declare @lens int
set @Years='2012'
set @Months='1'
set @Sort=2
set @str='10010001'
set @t='Z'
set @lens=4
declare @cc datetime
declare @Umon nvarchar(50)
declare @Dmon nvarchar(50)
declare @Col varchar(5)
declare @BMon nvarchar(50)
SET @Umon=convert(datetime,'01/01/'+@Years,101)
SET @Col='C'+@Months
SET @Dmon=convert(datetime,'12/31/'+@Years+' 23:59:59',120)
SET @cc=convert(datetime,(SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))),120)
SET @BMon=convert(datetime,@cc,101)
declare @sql nvarchar(200)
set @sql='B.Acc_No=1000'

declare @qcyed numeric(28, 8)

declare @temsql nvarchar(max)
set @temsql='select @tem_1=sum(a) from (select ISNULL(SUM(B.AMTN),0) a From Vdtl B,VHED H  Where  (H.MAK_NO=B.MAK_NO)  and  (B.VOH_ID >=''2'') AND (B.VOH_ID <=''7'') and(1=1) '+@sql+' And (((B.DEP=''00000000'') Or '
set @temsql=@temsql+'(Exists(Select DC.DEP_DW From DB_TD02.dbo.DEPT_CHK DC  Where (B.DEP=DC.DEP_DW  COLLATE database_default ) And ((DC.DEP_UP=''00000000'')))) ) Or (B.DEP Is Null) Or (B.DEP = '''')) And (1=1) And (IsNull(H.Cancel_ID,'''')<>''T'')'
set @temsql=@temsql+' And (H.MAK_DAT>='''
set @temsql=@temsql+@Umon
set @temsql=@temsql+''')  And (H.MAK_DAT<='''
set @temsql=@temsql+CONVERT(VARCHAR(20),@cc,120)
set @temsql=@temsql+''') '
set @temsql=@temsql+'Union All select ISNULL(SUM(B.AMTN),0) a From Vdtl B,VHED H  Where  (H.MAK_NO=B.MAK_NO)  and  (B.VOH_ID =''1'') and(1=1) '+@sql+' And (((B.DEP=''00000000'') Or '
set @temsql=@temsql+'(Exists(Select DC.DEP_DW From DB_TD02.dbo.DEPT_CHK DC  Where (B.DEP=DC.DEP_DW  COLLATE database_default ) And ((DC.DEP_UP=''00000000'')))) ) Or (B.DEP Is Null) Or (B.DEP = '''')) And (1=1) And (IsNull(H.Cancel_ID,'''')<>''T'') '
set @temsql=@temsql+' And (H.MAK_DAT>='''
set @temsql=@temsql+@Umon
set @temsql=@temsql+''')  And (H.MAK_DAT<='''
set @temsql=@temsql+@Dmon
set @temsql=@temsql+''') AND B.DC=''D'') AS t'
--print @temsql
exec sp_executesql @temsql,N'@tem_1 numeric(28, 8) output',@tem_1=@qcyed output

最后提示如下错误：
消息 102，级别 15，状态 1，第 1 行
'B' 附近有语法错误。
消息 102，级别 15，状态 1，第 1 行
'B' 附近有语法错误。
消息 102，级别 15，状态 1，第 1 行
')' 附近有语法错误。

#6

但是用print @temsql是正常的

引用 5 楼 jack4238 的回复:

declare @Years nvarchar(20)
declare @Months nvarchar(10)
declare @Sort int
declare @str nvarchar(2000)
declare @t varchar(5)
declare @lens int
set @Years='2012'
set @Months='1'
set @Sort=2
s……