SQL中的用户定义函数 [英] user-defined functions in SQL

问题描述

HI ,,,





任何人都知道如何在MS SQL中创建用户定义的函数。



对于Ex：

我需要一个名为addr（string cname）的函数，它可以获取输入字符串并返回某种字符串。







谢谢....

,,


Can anyone knows how to create user-defined functions in MS SQL.

For Ex:
I need a function called addr(string cname) which can take the input string and return some kind of string.



Thank You....

推荐答案

CREATE FUNCTION FUN_NAME(@parameter DATATYPE)
RETURN VARCHAR2(200)
AS
BEGIN
DECLARE @variable VARCHAR2(200)
// perform your operation here
// when you assign the value to variable use SET keyword
END
RETURN @variable

以下是致电功能的方式

Following is the way to call function

Select FUN_NAME(''parameterValue'');

主要是在SELECT语句中使用UDF来做一些事情根据列值计算或查找一些值，如下所示

Hi ,

Mainly UDF are used in SELECT Statement for doing some calculation or find some values based on Column values like as follows

CREATE FUNCTION [dbo].[fnNthIndex](@Input VARCHAR(8000), @Delimiter CHAR(1), @Ordinal INT)
/**************************************************************************
CREATED BY	: Prabu
CREATED DATE   : 21-Feb-2013
PURPOSE		: To Find the Nth occurrence of a substring

USAGE		:
			

		SELECT dbo.fnNthIndex('ü123ü124ü27339ü', 'ü', 2)
		

***************************************************************************/
RETURNS INT
AS
BEGIN
    DECLARE  @Pointer INT=1,@Last INT=0, @Count INT=1
    WHILE (2 > 1)
    BEGIN
        SET @Pointer = CHARINDEX(@Delimiter,@Input,@Pointer)
        IF @Pointer = 0
            BREAK
        IF @Count = @Ordinal
	   BEGIN
            SET @Last = @Pointer
            BREAK
        END
        SELECT @Count = @Count + 1, @Pointer = @Pointer + 1
      END
    RETURN @Last
END

用户定义的功能 [ ^ ]



了解何时在SQL Server中使用用户定义的函数 [ ^ ]



用户定义的函数 [ ^ ]



问候，

GVPrabu

User Defined Functions[^]

Understand when to use user-defined functions in SQL Server[^]

User-defined function[^]

Regards,
GVPrabu

CREATE FUNCTION [dbo].[udf_ReturnData] ( @myString VARCHAR(8000))
RETURNS @MyTable TABLE
   (
    Parameter1     int,
    Parameter2      varchar(10),
.
.
.
    Parametern
   )
--same way you can return any type of data
AS
BEGIN
 --your query goes here
--you can insert the data to @MyTable variable and return that table you will get your result
END
GO

了解更多详情请浏览这些链接，

点击此处 [ ]

http://blog.sqlauthority.com / 2007/09/08 / sql-server-udf-user-defined-function-get-number-of-days-in-month / [ ^ ]

这篇关于SQL中的用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！