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 defined

In user defined based on the return type it is classified, scalar , table valued 


/* Scalar value function RETURN TYPE IS INT */
CREATE FUNCTION CALSUM(@A INT,@B INT)
RETURNS INT
AS
BEGIN
      RETURN @A + @B;
END

SELECT DBO.CALSUM(2,3)

Output :
5

/* Function return varchar */
CREATE FUNCTION EMPID(@ID INT)
RETURNS VARCHAR(4)
AS
BEGIN

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

END

SELECT DBO.EMPID(3)

Output :
E3

/* Table value function */
 CREATE FUNCTION CALTAB(@START INT,@END INT)
 RETURNS @TAB TABLE(NO INT)
 AS
 BEGIN

      WHILE @START <= @END
      BEGIN
      
       INSERT INTO @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.

No comments:

Post a Comment