请教一下.
自定义的函数里面返回表,如果创建一个条件
create function fn_getuserreport(@begindate varchar(50),@enddate varchar(50))
returns table
as
declare @tb varchar(200)
set @tb='select * from transrecord where convert(datetime,convert(varchar,rc_createdate,112)) between convert(datetime,'''+@begindate+''') and convert(datetime,'''+@enddate+''')'return
(
select 1 from users
);
因为加了
declare @tb varchar(200)
set ....
就报错
消息 156,级别 15,状态 1,过程 fn_getuserreport,第 5 行
关键字 'declare' 附近有语法错误。
消息 178,级别 15,状态 1,过程 fn_getuserreport,第 8 行
在此上下文中不能使用带有返回值的 RETURN 语句。
自定义的函数里面返回表,如果创建一个条件
create function fn_getuserreport(@begindate varchar(50),@enddate varchar(50))
returns table
as
declare @tb varchar(200)
set @tb='select * from transrecord where convert(datetime,convert(varchar,rc_createdate,112)) between convert(datetime,'''+@begindate+''') and convert(datetime,'''+@enddate+''')'return
(
select 1 from users
);
因为加了
declare @tb varchar(200)
set ....
就报错
消息 156,级别 15,状态 1,过程 fn_getuserreport,第 5 行
关键字 'declare' 附近有语法错误。
消息 178,级别 15,状态 1,过程 fn_getuserreport,第 8 行
在此上下文中不能使用带有返回值的 RETURN 语句。
returns table
as
BEGIN
declare @tb varchar(200)
set @tb='select * from transrecord where convert(datetime,convert(varchar,rc_createdate,112)) between convert(datetime,'''+@begindate+''') and convert(datetime,'''+@enddate+''')'return
(
select 1 from users
);END
在此上下文中不能使用带有返回值的 RETURN 语句。
消息 102,级别 15,状态 31,过程 fn_getuserreport,第 13 行
'BEGIN' 附近有语法错误。
不能定义其他的,只能直接写一个return
begin
declare @tb varchar(200)
set @tb='select * from transrecord where convert(datetime,convert(varchar,rc_createdate,112)) between convert(datetime,'''+@begindate+''') and convert(datetime,'''+@enddate+''')'return
(
select 1 from users
);
end
go
create function ufn_getuserreport(@begindate varchar(50),@enddate varchar(50))
returns @tab table(id int) as
begin
declare @tb varchar(200)
-- 这是什么意思?函数中不能使用 exec 执行动态语句。
set @tb='select * from transrecord where convert(datetime,convert(varchar,rc_createdate,112)) between convert(datetime,'''+@begindate+''') and convert(datetime,'''+@enddate+''')';insert into @tab select 1 from users;return ;
end
go
範例
A. 使用計算 ISO 週的純量值使用者自訂函數
下列範例會建立使用者自訂函數 ISOweek。這個函數取用日期引數並計算 ISO 週數。若要使函數能夠正確計算,必須先叫用 SET DATEFIRST 1,才呼叫該函數。 這個範例也顯示利用 EXECUTE AS 子句來指定可執行預存程序的安全性內容。在這個範例中,選項 CALLER 指定將在呼叫程序的使用者之內容中執行程序。您可指定的其他選項有 SELF、OWNER 及 user_name。如需詳細資訊,請參閱<EXECUTE AS 子句 (Transact-SQL)>。 複製程式碼
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END;
GO
以下是函數呼叫。請注意,DATEFIRST 是設為 1。 複製程式碼
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
此為結果集。 複製程式碼
ISO Week
----------------
52
B. 建立內嵌資料表值函數
下列範例傳回內嵌資料表值函數。它傳回三個資料行:ProductID、Name,以及年初至今賣給商店之每項產品的總計彙總 YTD Total (依商店區分)。 複製程式碼
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.fn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.fn_SalesByStore;
GO
CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
若要叫用函數,請執行這項查詢。 複製程式碼
SELECT * FROM Sales.fn_SalesByStore (602);
C. 建立多重陳述式資料表值函數
下列範例建立資料表值函數 fn_FindReports(InEmpID)。當提供有效的員工識別碼時,函數會傳回對應於所有員工的資料表,該資料表會直接或間接報告至員工。函數利用遞迴一般資料表運算式 (CTE) 來產生階層式員工清單。如需有關遞迴 CTE 的詳細資訊,請參閱<WITH common_table_expression (Transact-SQL)>。 複製程式碼
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.fn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.fn_FindReports;
GO
CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.fn_FindReports(109)
ORDER BY Sort;
D. 建立 CLR 函數
下列範例假設 SQL Server Database Engine 範例 安裝在本機電腦的預設位置中,且已編譯 StringManipulate.csproj 範例應用程式。如需詳細資訊,請參閱<增補感知的字串操作>。 這個範例建立 CLR 函數 len_s。在建立這個函數之前,已在本機資料庫中註冊組件 SurrogateStringFunction.dll。 複製程式碼
DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\')
FROM master.sys.database_files
WHERE name = 'master';CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GOCREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO
最后要在WEB上分页select * from (exec(proc))这样似乎不行.所以才想到用 function
---
不是这样的.因为我里面有很多条语句.
return
(
select B.*
--盈利
,yl= ( ck+qk+xf+fd+zj+fj )
--对帐
,dz= ( case when (ck+qk+xf+fd+zj+fj )+zyk=u_balance then '对' else '错' end )
from
(
select
--用户名
u_name
--现余额
,u_balance
--冻结资金
,freezemone
...因此要先拼接一个语句.然后在要的地方执行 exec(@tb)