请问 多表值函数 如何执行动态拼接SQL语句

[love_study]

Sql 代码如下：
ALTER FUNCTION [dbo].[FunGetElecDataStream2]
(
@ComId INT,
@StationNo varchar(32),
@BeginYear INT,
@EndYear INT
)
RETURNS
@ElecData TABLE
(
comID INT,
stationNo varchar(32),
staName VARCHAR(64),
payYear INT,
payMonth VARCHAR(20),
price FLOAT,
kwh FLOAT
)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)
SET @SQL='INSERT INTO @ElecData SELECT comID,stationNo,staName,payYear,payMonth,price,kwh FROM V_StationElecDate WHERE'
IF(@ComId IS NULL)
SET @SQL=@SQL+' comID='+CAST(@comID AS NVARCHAR)+' AND'
IF(@StationNo IS NULL)
SET @SQL=@SQL+' stationNo='''+@StationNo+''' AND'
IF(@BeginYear IS NULL)
SET @SQL=@SQL+' payYear>='+CAST(@BeginYear AS NVARCHAR)+' AND'
IF(@EndYear IS NULL)
SET @SQL=@SQL+' payYear<='+CAST(@EndYear AS NVARCHAR)+' AND'
SET @SQL=@SQL+' 1=1'
EXEC sp_executesql @SQL
RETURN
END

执行函数
提示 "只有函数和扩展存储过程才能从函数内部执行。"
请问如何解决？

[htl258_Tony]

动态SQL不支持在自定义函数中执行，改用存储过程吧

[AcHerat]

函数是不允许有动态SQL的，自己写个存储过程吧！函数换成存储过程就OK。

[Varchar]

函数不能执行存储过程，不能动态语句

[中国风]

只有擴展存儲過程可以函數里用，如:SP_OA...
或用CLR寫函數

[中国风]

ALTER FUNCTION [dbo].[FunGetElecDataStream2] 
(
@ComId INT,
  @StationNo varchar(32),
  @BeginYear INT,
  @EndYear INT
)
RETURNS 
@ElecData TABLE 
(
  comID INT,
  stationNo varchar(32),
  staName VARCHAR(64),
  payYear INT,
  payMonth VARCHAR(20),
  price FLOAT,
  kwh FLOAT  
)  
AS
BEGIN
 
	INSERT INTO @ElecData SELECT comID,stationNo,staName,payYear,payMonth,price,kwh 
	FROM V_StationElecDate 
	WHERE comID=ISNULL(@ComId,0) AND stationNo=isnull(@StationNo,'') AND payYear BETWEEN ISNULL(@BeginYear,'1900-01-01') AND ISNULL(@EndYear,'1900-01-01')

RETURN 
END

看看結果是否與樓主的一致