Shulou(Shulou.com)05/31 Report--

In this article, the editor introduces in detail "how to create user-defined functions in SQL Server". The content is detailed, the steps are clear, and the details are handled properly. I hope that this article "how to create user-defined functions in SQL Server" can help you solve your doubts.

I. the definition of UDF

Much like a stored procedure, a user-defined function is an ordered set of T-SQL statements, and the UDF is pre-optimized and compiled and can be called as a unit.

The main difference between UDF and stored procedures is the way in which results are returned:

Parameters can be passed in when using UDF, but cannot be passed out. The concept of output parameters is replaced by more robust return values.

Like system functions, scalar values can be returned, and the advantage of this value is that it is not limited to shaping data types as in stored procedures, but can return most SQL Server data types.

There are two types of UDF:

Returns the UDF of a scalar value.

Returns the UDF of the table.

Create syntax:

CREATE FUNCTION [.] ([[AS] [.] [= [READONLY]] [,... n]]) RETURNS {| TABLE [()]} [WITH [ENCRYPTION] | [SCHEMABINDING] | [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] | [EXECUTE AS {CALLER | SELF | OWNER |}] [AS] {EXTERNAL NAME | BEGIN [] {RETURN 0) SELECT @ RESULT2+=CAST (S2 AS VARCHAR (1)) @ RESULT8+=CASE WHEN D8 AS VARCHAR 0 AND S8 AS VARCHAR 0 THEN''ELSE CAST (S8 AS VARCHAR (1)) END @ RESULT16+=CASE WHEN D160AND S160THEN''ELSE CASE CAST (S16 AS VARCHAR (5)) WHEN' 10' THEN'A 'WHEN' 11' THEN'B' WHEN '12' THEN' C' WHEN '13' THEN' D' WHEN '14' THEN' E' WHEN '15' THEN' F' ELSE CAST (S16 AS VARCHAR (5)) END END FROM CTE ORDER BY [INDEX] DESC INSERT INTO @ t_table SELECT @ RESULT2 @ RESULT8,@RESULT16 RETURN ENDGO

5. Understanding certainty

User-defined functions can be deterministic or non-deterministic. If given a specific set of valid inputs, each function can return the same result, then the function is said to be deterministic.

SUM () is a deterministic built-in function. The sum of 3, 5, and 10 is always 18, and the value of GETDATE () is non-deterministic because GETDATE () changes each time it is called.

If a view or computed column references a non-deterministic function, no indexes are allowed on that view or column.

If you decide whether the function is deterministic? In addition to the rules described above, this information is stored in the object's IsDeterministic property, which can be checked using the OBJECTPROPERTY property.

SELECT OBJECTPROPERTY (OBJECT_ID ('DateOnly'),' IsDeterministic');-- just the custom function just now

The output is as follows:

It's unexpectedly uncertain. The reason is that this "WITH SCHEMABINDING" was not added when the function was defined before.

ALTER FUNCTION dbo.DateOnly (@ Date date) RETURNS date WITH SCHEMABINDING-- when we add this sentence, AS BEGIN RETURN @ Date END

When the query is executed, the function is deterministic.

After reading this, the article "how to create user-defined functions in SQL Server" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.