定义一个函数并在sql查询中使用它

I was tring to create a function and use it in my little Query. I did not do this before and I do not really know to do it.

我正在尝试创建一个函数并在我的小查询中使用它。我以前没有这样做过，我也不知道怎么做。

Is it simply the same like any other languages? like

它和其他语言一样吗?就像

create function testFunction()...
begin
..
..
end

and then for example :

例如:

Select testFunction()

and it should work ?

它应该奏效吗?

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  
SET DATEFIRST 1
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week'

2 个解决方案

#1

Yes, SQL functions are easy to create. But you have to understand the 3 different types of functions in SQL:
1) Scalar functions:
-- return a single value.
2) Table based functions:
-- returns a Table.
3) Aggregate function:
returns a single value (but the function looped through a window set).

是的，SQL函数很容易创建。但是您必须理解SQL: 1)标量函数:——返回一个值。2)基于表的函数:——返回一个表。

creating a function in MS SQL Server 2012 by using template:

使用模板在MS SQL Server 2012中创建一个函数:

There some great reference resources for starting creating SQL functions: an example of User Defined Scalar Function (from dotnettricks):

有一些很好的开始创建SQL函数的参考资源:一个用户定义标量函数的例子(来自dotnettricks):

        --Create function to get emp full name 
        Create function fnGetEmpFullName
        (
          @FirstName varchar(50),
          @LastName varchar(50)
        )
        returns varchar(101)
        AS
        BEGIN 
          return (Select @FirstName + ' '+ @LastName);
        END

Microsoft reference, explanation and examples in this link
informit article on User Defined Functions in this link2
and finally from SQL Team website, an introduction to User Defined Functions

微软参考，解释和例子在这个链接informit文章用户定义函数在这个链接2，最后从SQL团队网站，介绍用户定义函数

#2

This is a bit broad, do you want to return a table? a value?.. There are many different things that a function can do, a sample syntax would be:

这有点太宽泛了，你想退掉一张桌子吗?一个值吗?。函数可以做很多不同的事情，例如:

CREATE FUNCTION Schema.FunctionName
(
@InputValue InputValueDataType --Parameters here
)
RETURNS ReturnDataType -- The data type you want to return it
AS
BEGIN
    -- Do some things with @InputValue
END

The Microsoft Doc is HERE it explains the different ways to do it

微软文档在这里它解释了不同的方法

Then you can just do SELECT * FROM schema.FunctionName (@InputValue) to return it

然后你可以从模式中选择*。FunctionName (@InputValue)返回它

#1