How to Create Sql Function, use of function in sql

-

SQL SERVER FUNCTIONS:



In this Post we learn how to create and drop functions in SQL Server 2008 with examples. As we know that Function is a segment of code which is use to solve a particular function.

WHAT IS A FUNCTION IN SQL SERVER?

In SQL Server, a function is a stored program that you can pass parameters into and return a value.

SQL CREATE FUNCTION:

You can create your own functions in SQL Server. Let's take a closer look.

SQl function Syntax:

The syntax to create a function in SQL Server is:

CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype
    [ = default ] [ READONLY ]
  , @parameter [ AS ] [type_schema_name.] datatype
    [ = default ] [ READONLY ] ]
)
RETURNS return_datatype
[ WITH { ENCRYPTION | SCHEMABINDING  | RETURNS NULL ON NULL INPUT  | CALLED ON NULL INPUT       | EXECUTE AS Clause ][ AS ]  
BEGIN
   [declaration_section]
   executable_section
   RETURN return_value
END;

here in sql function:-
  • Schema_name is the name of the schema that owns the function.
  • Function_name is the name to assign to this function in SQL Server.
  • @parameter is one or more parameters passed into the function.
  • type_schema_name is the schema that owns the data type, if applicable.
  • Data type is the data type for @parameter.
  • Default is a default value to assign to @parameter.
  • READONLY means that @parameter cannot be overwritten by the function.
  • return_datatype is the datatype of the function's return value.
  • ENCRYPTION means that the source for the function will not be stored as plain text in the system views in SQL Server.
  • SCHEMABINDING means that the underlying objects cannot be modified so as to affect the function.
  • RETURNS NULL ON NULL INPUT means that the function will return NULL if any parameters are NULL without having to execute the function.
  • CALL ON NULL INPUT means that the function will execute the function even if any parameters are NULL.
  • EXECUTE AS clause sets the security context to execute the function.
  • return_value is the value returned by the function.

SQL Function Example:

Let's look at an example of how to create a function in SQL Server.
The following is a simple example of a function:

CREATE FUNCTION ReturnSite( @id INT )
RETURNS VARCHAR(50)
AS
BEGIN
   DECLARE @name VARCHAR(50);
   IF @site_id < 10
      SET @name = 'asp_net.com';
   ELSE
      SET @name = 'CheckYourMath.com';

   RETURN @name;
END;
This function is called ReturnSite. It has one parameter called @id which is an INT datatype. The function returns a VARCHAR(50) value, as specified by the RETURNS clause.

How to use SQL Function:

USE [test]
GO
SELECT dbo.ReturnSite(8);
GO

Sql Server Interview Qus:




Predefined SQL Database FUNCTIONS:

  • COUNT Function              Return the count of an expression
  • SUM Function   Return the sum of an expression
  • MIN Function    Return the min of an expression
  • MAX Function   Return the max of an expression
  • AVG Function    Return the average of an expression



Asp.net Dropdownlist Related Post:

Comments

Post a Comment