Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Wednesday, October 15, 2014

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:

No comments:

Post a Comment