STORED PRCEDURES

A STORED PROCEDURE IS NOTHING BUT A PREPARED SQL CODE THAT WE CAN SAVE AND WE CAN REUSE THE CODE OVER AND OVER AGAIN.  SO IF YOU WANT TO EXECUTE A QUERY AGAIN AND AGAIN, INSTEAD OF WRITING THE QUERY EACH TIME WE CAN SAVE IT AS A STORED PROCEDURE AND THEN JUST CALL THE STORED PROCEDURE TO EXECUTE THE SQL CODE THAT YOU SAVED AS PART OF THE STORED PROCEDURE.

IN ADDITION TO RUNNING THE SAME SQL CODE OVER AND OVER AGAIN YOU ALSO HAVE THE ABILITY TO PASS PARAMETERS TO THE STORED PROCEDURE, SO DEPENDING ON WHAT THE NEED IS THE STORED PROCEDURE CAN ACT ACCORDINGLY BASED ON THE PARAMETER VALUES THAT WERE PASSED.

TO FIND THE STORED PROCEDURES.

   GOTO DATABASE->PROGRAMABILITY->STOREDPROCEDURES

WE HAVE THREE TYPES OF STORED PROCEDURES

I)   SYSTEM STORED PROCEDURES  :-

 SYSTEM STORED PROCEDURES ARE THE ONE WHICH ARE DEVELOPED BY THE MICROSOFT. WE WILL GET THOSE SP'S AS PART OF THE SQL SERVER INSTALLATION.

  Example:-               A)  SP_HELPDB <DATABASENAME>

                                   B)  SP_ADDMESSAGE

                                   C)  SP_BINDEFAULT

II)  USER DEFINED STORED PROCEDURES  :-

USER DEFINED STORED PROCEDURES ARE THE ONE WHICH ARE CREATED BY THE USER.

SYNTAX:-

CREATE PROC <PROCEDURENAME>

AS

BEGIN

<FUNCTIONALITY>

END

TO EXECUTE THE STORED PROCEDURE 

EXEC <STOREDPROCEDURENAME> OR <STOREDPROCEDURENAME>

WE CAN WRITE STORED PROCEDURE IN 3 TYPES

1) SIMPLE STORED PROCEDURE

2) OUTPUTPARAMETER STOREDPROCEDURE

3)  RETURN VALUE

SIMPLE STORE PROCEDURES

IF WE ARE CREATING THE STORED PROCEDURE WITHOUT ANY PARAMETERS(OUTPUT AND RETURN) THEN WE CALL THAT STORED PROCEDURE AS SIMPLE STORED PROCEDURES.

EXAMPLE:

A)

CREATE PROC USP_PRODUCTDATA

 AS

   BEGIN

        SELECT * FROM DIMPRODUCT

   END

-->  TO CALL THE ABOVE STORED PROCEDURE

       

          EXEC USP_PRODUCTDATA

-->  CREATING A STORED PROCEDURE WITH INPUT PARAMETERS

B)

CREATE PROC USP_PRODUCTDATABYCOLOR (@COLOR VARCHAR(20))

AS

   BEGIN

        SELECT * FROM DIMPRODUCT WHERE COLOR=@COLOR

   END

-->TO CALL THE ABOVE STORED PROCEDURE

                  EXEC USP_PRODUCTDATA 'RED'

-->CREATING A STORED PROCEDURE WITH MULTIPLE INPUT PARAMETERS

C)

CREATE PROC USP_PRODUCTDATABYCOLOR (@ID INT,@COLOR VARCHAR(20))

AS

   BEGIN

      SELECT * FROM DIMPRODUCT WHERE PRODUCTKEY=@ID AND COLOR=@COLOR

   END

-->TO CALL THE ABOVE STORED PROCEDURE

               EXEC USP_PRODUCTDATA 10,'RED'

-->CREATING A STORED PROCEDURE WITH INPUT PARAMETERS AND WITH DEFAULT VALUE

D)

CREATE PROC USP_PRODUCTDATABYCOLOR (@ID INT,@COLOR VARCHAR(20)='RED')

AS

   BEGIN

         SELECT * FROM DIMPRODUCT WHERE PRODUCTKEY=@ID AND COLOR=@COLOR

   END

-->TO CALL THE ABOVE STORED PROCEDURE

             1.   EXEC USP_PRODUCTDATA 10,'BLACK'

             2.   EXEC USP_PRODUCTDATA 10

   (IF WE PASS ANY VALUE FOR COLOR THEN IT WILL TAKE THAT COLOR AND IF WE DON'T SPECIFY THE COLOR THEN IT'LL TAKE THE DEFAULT VALUE)

OUTPUTPARAMETER STOREDPROCEDURE

IF WE WANT TO USE THE RESULT OF THE STORED PROCEDURE AND IF YOU WANT TO USE IT IN ANOTHER  STORED PROCEDURE THEN FIRST WE STORE THE VALUE OF THE STORED PROCEDURE IN ONE VARIABLE I.E. OUTPUT PARAMETER AND WE USE THE OUTPUT PARAMETER AS INPUT PARAMETER IN ANOTHER STORED PROCEDURE. IN OUTPUT PARAMETER WE CAN USE ANY DATA TYPE.

EXAMPLE:-

   

CREATE PROC USP_PRODUCTCOLOWISESALES

                            (@COLOR VARCHAR(20),@SALES MONEY OUTPUT)

       AS

            BEGIN

                   SELECT SUM(SALESAMOUNT) FROM DIMPRODUCT A

                   JOIN FACTINTERNETSALES B ON A.PRODUCTKEY=B.PRODUCTKEY

                   WHERE COLOR=@COLOR

            END

-->TO CALL THE ABOVE STORED PROCEDURE

            DECLARE @X MONEY

            EXEC USP_PRODUCTCOLOWISESALES 'RED',@X OUTPUT

            PRINT @X

RETURN VALUE STORED PROCEDURES

RETURN VALUES CAN BE USED WITHIN STORED PROCEDURES TO PROVIDE THE STORED PROCEDURE EXECUTION STATUS TO THE CALLING PROGRAM. WE CAN CREATE OUR OWN PARAMETER THAT CAN BE PASSED BACK TO THE CALLING PROGRAM. BY DEFAULT, THE SUCCESSFUL EXECUTION OF A STORED PROCEDURE WILL RETURN '0'.

SCENARIO :-

WE CREATE A STORED PROCEDURE TO AVOID DUPLICATE RECORD INSERTION IN THE SQL SERVER DATABASE PRIOR TO INSERTION INTO THE DATABASE. IF WE INSERT A DUPLICATE RECORD IN THE TABLE THEN EXECUTION OF A STORED PROCEDURE WILL RETURN A STATUS VALUE WHATEVER WE GIVEN. IF WE INSERT A RECORD INTO THE TABLE WHICH ARE NOT AVAILABLE I.E. NOT DUPLICATES THEN SUCCESSFUL EXECUTION OF A STORED PROCEDURE WILL RETURN '0'.

CREATE TABLE EMPLOYEE (ID INT IDENTITY(1,1),NAME VARCHAR(20))

EXMPLE :-

CREATE PROC USP_VALIDATEINSERTNAME (@NAME VARCHAR(100))

AS

DECLARE @RETURNVALUE INT

BEGIN TRAN

IF EXISTS

(

SELECT * FROM EMPLOYEE WHERE NAME=@NAME

)

BEGIN

SET @RETURNVALUE=1

END

ELSE

BEGIN

INSERT INTO EMPLOYEE (NAME) VALUES (@NAME)

SET @RETURNVALUE = @@ERROR

END

IF @RETURNVALUE <>0

BEGIN

PRINT 'DUPLICATE RECORD CAN NOT BE INSERTED'

ROLLBACK TRAN

END

ELSE

BEGIN

PRINT 'UNIQUE RECORD'

COMMIT TRAN

END

RETURN @RETURNVALUE

CREATING A STORED PROCEDURE WITH 'NOCOUNT ON'

THIS  IS TO REDUCE THE AMOUNT OF NETWORK DATA FOR EACH STATEMENT THAT OCCURS WITHIN  THE STORED PROCEDURES. EVERY TIME A SQL STATEMENT IS EXECUTED IT RETURNS THE NUMBER OF ROWS THAT WERE AFFECTED. BY USING 'SET NOCOUNT ON' WITHIN THE STORED PROCEDURE WE CAN SHUTTOFF THESE MESSAGES AND REDUCE SOME TRAFFIC.

CREATE PROC USP_VALIDATEINSERTNAME (@NAME VARCHAR(100))

AS

DECLARE @RETURNVALUE INT

SET NOCOUNT ON

BEGIN TRAN

IF EXISTS

(

SELECT * FROM EMPLOYEE WHERE NAME=@NAME

)

BEGIN

SET @RETURNVALUE=1

END

ELSE

BEGIN

INSERT INTO EMPLOYEE (NAME) VALUES (@NAME)

SET @RETURNVALUE = @@ERROR

END

IF @RETURNVALUE <>0

BEGIN

PRINT 'DUPLICATE RECORD CAN NOT BE INSERTED'

ROLLBACK TRAN

END

ELSE

BEGIN

PRINT 'UNIQUE RECORD'

COMMIT TRAN

END

RETURN @RETURNVALUE

-->TO VIEW THE CODE OF USER DEFINED STORED PROCEDURE

   SP_HELPTEXT <PROCEDURENAME>

-->We can Create The Stored Procedure with other Two Options

        i.            RECOMPILE: MEANS WHENEVER IT EXECUTES RECOMPILE THE EXECUTION PLAN.

      ii.            ENCRYPTION: THE CODE WILL BE ENCRYPTED.

1. RECOMPILE

CREATE PROC <PROCNAME> (@PRAMETERNAME DATATYPE,@PARAMNAME1 DATATYPE OUTPUT)                       

                            

   WITH  RECOMPLIE

   AS

   BEGIN

   <FUNCTIONALITY>

   END

2) ENCRYPTION

CREATE PROC <PROCENAME> (@PRAMETERNAME DATATYPE,@PARAMNAME1 DATATYPE OUTPUT)

                             

   WITH  ENCRYPTION

   AS

   BEGIN

   <FUNCTIONALITY>

   END

ADVANTAGES OF USING STORED PROCEDURES

a.      REDUCE NETWORK USAGE BETWEEN CLIENT AND SERVERS.

b.      IMPROVED SECURITY-DBA CAN CONTROL THE USERS WHO ACCESS THE STORED PROCEDURE.

c.       DEVELOPMENT COST AND INCREASED RELIABILITY.

d.      SP'S ARE TUNABLE TO IMPROVE THE PERFORMANCE. WHEN SAME SP EXECUTED AGAIN, IT CAN USE THE    PREVIOUS CACHED EXECUTION PLANS.

e.      CODE REUSABILITY.

DISADVANTAGES OF USING STORED PROCEDURES

a.      WRITING AND MAINTAINING STORED PROCEDURES REQUIRES MORE SPECIALIZED SKILLS.

b.      THERE ARE NO DEBUGGER AVAILABLE FOR SP'S.

c.       SP LANGUAGE MAY DIFFER FROM ONE DATABASE SYSTEM TO ANOTHER.

d.      POOR EXCEPTION HANDLING.