Function in SQL





Function in Sql.

Just like any other method in C# .we have function ‘s in sql server.

Definition of a Function

Function performs an operation and returns a value.Its main purpose is encapsulate code for reuse.

Here in this section, we learn how to create and use the three types of User defined sections:

  • Scalar – valued
  • Inline
  • Multi-statement or Table-Valued

In general User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement

Scalar Function

Returns a single value
Sample of Scalar Function:
Implementation

Create Function testFunction(@Num1 int, @Num2 int)
returns  int
as
begin
return (@Num1+@Num2)
end
Go
 

The following SELECT statement calls the function. Note that the two-part name (owner.object_name) is required when calling this function.Here in this case we need to give db.testFunction

SELECT  dbo.testFunction(1,4)

Inline Function

Inline user-defined functions  returns a table.
Sample of Inline Function:

Implementation

Create FUNCTION testFunctionTable (@recipient varchar(20))
RETURNS TABLE
AS
RETURN
(
    SELECT FirstName FROM dbo.NameDemo WHERE LastName = @recipient
)

Implementation

SELECT FirstName from dbo.testFunctionTable ('Joseph')

Multi-statement or Table-Valued

As with the regular In line,returns only a single table. However, here we can can create a table, populate it, then return the results.
Implementation

create function dbo.multiLinesdDemo(@FirstName as nvarchar(50), @lastNameB as nvarchar(50))
        returns @ManyPeople table
          (PersonID int, FullName nvarchar(101), PhoneNumber nvarchar(25))
as
begin
 
  insert @ManyPeople (PersonID, FullName, PhoneNumber)
    select ContactID
        , FirstName + ‘ ‘ + LastName
        , Phone
      from Person.Contact
    where LastName like (@lastNameA + ‘%’);
 
  insert @ManyPeople (PersonID, FullName, PhoneNumber)
    select ContactID
        , FirstName + ‘ ‘ + LastName
        , Phone
      from Person.Contact
    where LastName like (@lastNameB + ‘%’);
 
  return
end

HappyCoding ! 😉

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: