Pages

Tuesday, 5 November 2013

FUNCTION IN SQL SERVER

In this article we are going to see how to create a function in sql server, Two types of functions in sql server 1. system defined 2. user definedIn user defined based on the return type it is classified, scalar , table valued

/* Scalar value
function RETURN TYPE IS INT */

CREATEFUNCTION CALSUM(@A INT,@B INT)

RETURNSINT

AS

BEGIN

RETURN @A + @B;

END

SELECT DBO.CALSUM(2,3)

Output :

5

/* Function
return varchar */

CREATEFUNCTION EMPID(@ID INT)

RETURNSVARCHAR(4)

AS

BEGIN

RETURN'E'+CAST(ISNULL(@ID,0)ASVARCHAR(3))

END

SELECT DBO.EMPID(3)

Output :

E3

/* Table value
function */

CREATEFUNCTION CALTAB(@START
INT,@END INT)

RETURNS @TAB TABLE(NOINT)

AS

BEGIN

WHILE
@START <= @END

BEGIN

INSERTINTO @TAB(NO)SELECT @START

SELECT
@START = @START +
1

END

RETURN

END

SELECT*FROM dbo.caltab(1,10)

Output :

1

2

3

4

5

6

7

8

9

10

From this article you can learn how to create a function in sql server.