示例函数: create function jon() returns int as begin declare @i int set @i=200 return (@i) end在你的宿主程序中:SQL语句写为: select dbo.jon() 或者 select a=dbo.jon() 你把它当作常规的查询SQL语句处理,它返回的就是一行一列的数据集 后面个的列名就是 a 然后你应用程序去取就可以了
呵呵,急了是不是~A. 计算 ISO 周的标量值用户定义函数 下例中,用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数,必须在调用该函数前唤醒调用 SET DATEFIRST 1。 CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int 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下面是函数调用。注意 DATEFIRST 设置为 1。SET DATEFIRST 1 SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week' 下面是结果集。ISO Week ---------------- 52B. 内嵌表值函数 下例返回内嵌表值函数。USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)C. 多语句表值函数 假设有一个表代表如下的层次关系: CREATE TABLE employees (empid nchar(5) PRIMARY KEY, empname nvarchar(50), mgrid nchar(5) REFERENCES employees(empid), title nvarchar(30) )表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID,它返回与所有直接或间接向给定职员报告的职员相对应的表。该逻辑无法在单个查询中表现出来,不过可以实现为用户定义函数。 CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int -- table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees ed 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END
-- copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO-- Example invocation SELECT * FROM fn_FindReports('11234') GO
create function jon()
returns int
as
begin
declare @i int
set @i=200
return (@i)
end在你的宿主程序中:SQL语句写为: select dbo.jon() 或者 select a=dbo.jon()
你把它当作常规的查询SQL语句处理,它返回的就是一行一列的数据集 后面个的列名就是 a
然后你应用程序去取就可以了
下例中,用户定义函数 ISOweek 取日期参数并计算 ISO 周数。为了正确计算该函数,必须在调用该函数前唤醒调用 SET DATEFIRST 1。 CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
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下面是函数调用。注意 DATEFIRST 设置为 1。SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是结果集。ISO Week
----------------
52B. 内嵌表值函数
下例返回内嵌表值函数。USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)C. 多语句表值函数
假设有一个表代表如下的层次关系: CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID,它返回与所有直接或间接向给定职员报告的职员相对应的表。该逻辑无法在单个查询中表现出来,不过可以实现为用户定义函数。 CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
C程序中能直接用这种 :select dbo.jon() 或者 select a=dbo.jon() ?
应该这个语句只能作为一个字符串吧? 在字符串中, a只是一个字符,不是一个独立的变量.不能解决问题的.
如果返回类型是变量的话,
select dbo.jon() --这一句错~
select a=dbo.jon()--这一句返回如下:
a
返回值如果返回表的话,
--调用方法:
select * from dbo.jon()
应该这个语句只能作为一个字符串吧? 在字符串中, a只是一个字符,不是一个独立的变量.不能解决问题的.
-----------------------------------
程序中可以直接用select a=dbo.jon()
string str='select a=dbo.jon()'
与你平时用的: string str='select a from tb' 一样去理解这里的a不是你应用程序里面的变量赋值的含义,这是在SQL语句中指定列名的一中方法,
你用 string str='select dbo.jon() as a' 也可以
总是就是不能达到这种效果:
int fun(int a){ return a;}void otherfun(){
int b,c=10;
b=fun(c);
printf(b);
}
我在C#中都已经测试过了,在宿主程序中已得到值函数返回值200
至于你取不到值,不知道你怎么取的 :)
----
我不信你这个a还能拿出来用.