Stored Procedures & User Defined Functions

2. Named Batches: Set of T-SQL statements can written and executed as a single unit with a proper name called Named batch. These includes

1. Stored procedures
2. User defined Functions
3. Triggers

1. Stored Procedures: Stored procedures are one of the database objects. There are two types of stored procedures available in SQL Server.

1. System Defined Stored Procedures
2. User Defined Stored Procedures

System Defined Stored Procedures: These are also known as predefined or built-in stored procedures.

E.g.: 

SP_HELP

SP_RENAMEDB

SP_RENAME

SP_HELPCONSTRAINT

SP_HELPTEXT

------

-----

-------

User Defined Stored Procedures: Procedures created by the user are called used defined stored procedures.

Syntax:

 CREATE PROC [EDURE] PROCEDURENAME

            [@PARA 1 DATATYPE (SIZE)[=DEFAULT_VALUE][OUTPUT]

            @PARA 2 DATATYPE (SIZE)[=DEFAULT_VALUE][VALUE],….]

AS

BEGIN

SELECT STATEMENT

END

Syntax to execute the user defined stored procedure:

EXEC [UTE] PROCEDURENAME [VALUE1,VALUE2,…]

Note: The number of values supplied through EXEC statement must be equal to the number parameters.

E.g.1: Write a procedure to select the data from EMP table.

CREATE PROCEDURE P1

AS

BEGIN

SELECT * FROM EMP

END

      EXEC P1

E.g.2: Write a procedure to select the data from EMP table based on user supplied DEPTNO.

CREATE PROCEDURE P2 @X INT

AS

BEGIN

SELECT * FROM EMP WHERE DEPTNO=@X

END

EXEC P2 20

E.g.3: Write a procedure to add two numbers

CREATE PROCEDURE P3 @A INT=10,@B INT=20

AS

BEGIN

DECLARE @C INT

SET @C=@A+@B

PRINT @C

END

EXEC P3

Output: 30

EXEC P3 25, 45

Output: 70

Note: Server will give highest priority to the user supplied values rather than default values.

USER DEFINED FUNCTIONS: Functions created by user are called user defined functions

Types of user defined functions:

1.      SCALAR VALUED FUNCTIONS

2.       TABLE VALUED FUNCTIONS

Scalar valued functions: These functions will return a scalar value to the calling environment

Syntax:

 CREATE FUNCTION < FUNCTION_NAME> (@PARA 1 DATA TYPE ,

@ PARA 2 DATATYPE ,…..)

RETURNS <DATATYPE>

AS

BEGIN

DECLARE  @VARIABLE  DATATYPE

--------

----------

RETURN  @VARIABLE

END

Syntax to execute the user defined function:

SELECT/PRINT DBO.FUNCTIONNAME (VALUE1,VALUE2,……….)

Note: The number of values supplied through PRINT/SELECT statement must be equal to the number parameters.

E.g.1: Write a function to find the product of two numbers

 CREATE FUNCTION F1 (@ A INT, @B INT)

 RETURNS INT

 AS

BEGIN

DECLARE @ C INT

SET @C = @A * @B

RETURN @C

END

SELECT/PRINT DBO.F1 (3,5)

E.g.2: Write function to find the net salary of an employee read  EMPNO though parameter and display the net to return value

 CREATE FUNCTION F2 (@ VNO INT)

            RETURNS  INT

            AS

BEGIN

            DECLARE @ VSAL INT, @VCOM INT, @NET INT

            SELECT @VSAL =  SAL, @VCOM=COM

FROM EMP WHERE EMPNO =@VNO

IF @ VCOM IS NULL

BEGIN

PRINT ‘COMMISION IS NULL’

SET @NET = @VSAL

END

ELSE

BEGIN

SET @ NET = @VSAL + @VCOM

END

RETURN (@NET)

END

PRINT/SELECT DBO.F2(22)

2) Table valued function: These functions will return entire table to the calling environment.

Syntax:

CREATE FUNCTION <FUNCTION_NAME>(PARA 1 DATA TYPE ……….)

RETURNS TABLE

AS

BEGIN

<FUNCTION BODY>

RETURN (SELECT STATEMENT)

END

E.g.1: Write a function to return entire dept table

CREATE FUNCTION F3()

RETURNS TABLE

AS

BEGIN

RETURN (SELECT * FROM DEPT)

END

SELECT * FROM F3()

DEPT	DNAME	LOC

E.g2:

CREATE FUNCTION F4()

RETURN TABLE

AS BEGIN

RETURN(SELECT ENAME, DNAME FROM EMP, DEPT

                      WHERE EMP.DEPTNO = DEPT.DEPTNO)

END

SELECT * FROM F4()

ENAME                                 DNAME

SMITH                        RESEARCH

MILLER                     ACCOUNTING