==================================================================================================================================================================================== STORED PROCEDURES ==================================================================================================================================================================================== => IT IS A COLLECTION OF ONE OR MORE T-SQL STATEMENTS SAVED IN DATABASE FOR FUTURE USE 1 --VERIFY UR ACCOUNT NUMBER -SQLSTATEMENT 2. --- SELECT KIND OF TRANSACTION -SQL STATEMENT 3...FOR WITHDRAWL --- CHECK UR BALANCE --SQL STATEMENT 4. UPDATE UR ACCOUNT DETAILS -SQL STATEMENT ==================================================================================================================================================================================== STORED PROCEDURES--- ==================================================================================================================================================================================== => A STORED PROCEDURE IS AN EXECUTABLE DATABASE OBJECT WHICH CONTAINS ONE OR MORE SQL STATEMENTS. TASK --- OPEN THE LOCK--- 1. TAKE A KEY --- 2. INSERT THE KEY IN LOCK --- 3. ROTATE THE KEY IN PROEPER DIRECTION PROCEDURE : A SEQUENCE OF INSTRUCTIONS USED TO COMPLETE A TASK TASK : SEQUENCE OF INSTRUNCTIONS ------ SQL STATEMENTS SQL SQL SQL => WITH IN STORED PROCEDURE--WE CAN DML , DDL, VIEWS, STORED PROCEDURES..... => THESE PROGRAMS ARE STORED IN THE DATABASE THAT''S WHY THEY ARE KNOWN AS STORED PROCEDURE ADVANTAGES: =========== 1.REUSABILITY : =========== =>AS STORED PROCEDURES ARE SAVED IN DB THEY CAN BE REUSED BY USER OR AN APPLICATION PROGRAM CONNECTED TO DB. 2.SECURITY ========== BECAUSE STORED PROC ARE STORED IN DB SO ONLY AUTHORIZED PERSON/APPLICATION CAN EXECUTE THESE 3. CAN BE CALLED FROM FRONT END APPLICATIONS: ============================================ STORED PROCEDURES CAN BE CALLED FROM FRONT END APPLICATIONS LIKE JAVA, .NET.. WEBSITE ---CLICK ON MOBILE APPLICATION PROGRAM --- PARAMETER IS CAPTURED BY DROP PROCEDURE EMPDEPTWISE CREATE PROCEDURE EMPDEPTWISE @EMPNO VARCHAR(20)--------------------------ARGUMENT OR PARAMETER TO THE PROCEDURE----------INPUT PARAMETER AS SELECT * FROM EMP1 WHERE EMPNO = @EMPNO EXECUTE EMPDEPTWISE 7788 Msg 201, Level 16, State 4, Procedure EMPDEPTWISE, Line 0 [Batch Start Line 67] Procedure or function 'EMPDEPTWISE' expects parameter '@EMPNO', which was not supplied. 4. IMPROVES PERFORMANCE ============================ FIRST TIME STORED PROC WILL BE EXECUTED, EACH SQL STATEMENT WILL BE COMPILED AND EXECUTED TO CREATE AN EXECUTION PLAN. THEN THE PROCEDURE IS STORED IN COMPILED FORM WITH IN THE DATABASE. FOR EACH SUBSEQUENT EXECUTION, THE ALREADY COMPILED SQL STATEMENTS ARE EXECUTED. FIRST TIME EXECUTION OF STORED--- TWO STEPS WILL HAPPEN STEP - 1.COMPILATION -----------IT WILL GENERATE SOME EXECUTION PLAN AUTOMATICALLY BY THE SQL SERVER WHICH IS BEST SUITASBLE FOR IT.. STEP - 2.EXECUTION => THEN THE PROCEDURE IS STORED IN COMPILED FORM WITH IN THE DATABASE. STPE - 1 SUBSEQUENT EXECUTION ==> PRECOMPILED CODE WILL BE SENT FOR EXECUTION SYNTAX: ======= CREATE PROC|PROCEDURE [PARAMETER DECLARATION] [WITH [RECOMPILE] [,ENCRYPTION] AS SQL STATEMENTS ------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------- PARAMETERS: =>PARAMTERS ARE USED TO PASS VALUES FROM CALLING PROGRAMMING TO STORED PROC OR FROM STORED PROC TO CALLING PROGRAM => PARAMETERS ARE OF TWO TYPES: 1. INPUT PARAMETER 2. OUTPUT PARAMETER INPUT PARAMETER: USED TO PASS VALUES FROM CALLING PROGRAM TO STORED PROC. IT ALWAYS RECEIVES THE VALUE IT IS DEFAULT EXAMPLE : @CATEGORY VARCHAR(20)='DEFAULT' OUTPUT PARAMETER: USED TO PASS VALUES FROM STORED PROC TO CALLING PROGRAM IT RETURNS THE VALUE OR SENDS THE VALUE EXAMPLE : @SAL MONEY OUTPUT [RECOMPILE] ----> IT HAS TO BE COMPILED EACH TIME IT RUNS. [ENCRYPTION]----> PREVENTS USER FROM VIEWING CODE =================================================================== CREATE A STORED PROCEDURE WHICH DISPLAYS INFORMATION ABOUT DEPTNO 30 IN EMP1 ALTER PROC SPEMP1_1------------PARAMETER LESS OR NO PARAMETER PROCEDURE AS SELECT * FROM EMP1 WHERE DEPT = 30; EXECUTION OF STORED PROCEDURE: ============================ EXEC | EXECUTE PROCEDURENAME EXEC SPEMP1_1 =========================================================== CREATE A STORED PROCEDURE WHICH TAKES FROM USER EMPNO AND DISPLAY HIS DETAILS EMPNO, NAME,SALARY ALTER PROC SPEMP1_2 @ENO INT------------INPUT PARAMETER AS SELECT EMPNO, ENAME, SAL FROM EMP1 WHERE EMPNO = @ENO ======================================== EXECUTION OF STORE PROC EXEC SPEMP1_2 7788 ==================================================================================== =>TO MAKE THE CHANGES IN EXISTING STORED PROCEDURE WE CAN USE ALTER COMMAND => THE SYNTAX OF CREATING STORED PROC AND ALTERING STORED PROC IS VERY MUCH SIMILAR WITH ONLY DIFFERENCE IS OF THE WORD CREATE/ALTER --PURPOSE OR DESCRIPTION : --CREATED BY : HARSHIT BHADIYADRA --CREATED DATE : 12-JULY-2020 ALTER PROC SPEMP1_1 AS SELECT * FROM EMP1 WHERE DEPT = 20; EXEC SPEMP1_1 SELECT * FROM SYS.PROCEDURES SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ============================================= WE CAN SUPPLY THE PARAMETER VALUES BY EITHER POSITION OR NAMED PARAMETERS 1. POSTIONAL PARAMETERS 2. NAMED PARAMETERS CREATE PROC SPEMP1_122 @DEPTNO INT, @JOB VARCHAR(20) AS SELECT * FROM EMP1 WHERE DEPT = @DEPTNO AND JOB = @JOB; SUPPLYING PARAMETER VALUES BY POSITION: IN THIS CASE ORDER MATTERS: EXEC SPEMP1_122 30, 'SALESMAN'----------POSITIONAL PARAMETER EXECUTION EXEC SPEMP1_122 @JOB ='SALESMAN', @DEPTNO =30--------------NAMED PARAMETER EXECUTION EXEC SPEMP1_122 'SALESMAN', 30--------------NAMED PARAMETER EXECUTION ================================================== SUPPLYING PARAMTERS VALUES BY NAME: IN THIS CASE ORDER OF PARAMETERS DOES NOT MATTER WHILE CALLING THE STORED PROC EXEC SPEMP1_1 @JOB='SALESMAN', @DEPTNO = 30 ================================================= MAKING ARGUMENTS/ PARAMETER OPTIONAL =================================== WE CAN MAKE PARAMETERS TO BE OPTIONAL PARAMETERS BY ASSIGNING SOME DEFAULT VALUES. SO IF USER DOES NOT SUPPLY THOSE ARGUMENTS WHILE EXECUTING/CALLING STORED PROC THEN THESE DEFAULT VALUES WILL BE USED. => BUT IF USER SUPPLIES THE VALUES OF THESE PARAMETERS THEN THE DEFAULT VALUES WILL BE OVER WRITTEN WHICH MEANS THE DEFAULT VALUE WILL BE REPLACED BY THE VALUES THE CALLER HAS SUPPLIED. ALTER PROC SPEMP1_1 @DEPTNO INT = 30,-------------------DEFAULT PARAMETER DECLARATION @JOB VARCHAR(20) = 'CLERK'--------------DEFAULT PARAMETER DECLARATION AS SELECT * FROM EMP1 WHERE DEPT = @DEPTNO AND JOB = @JOB; ==================================== EXEC SPEMP1_1 ----> WILL USE DEFAULT VALUES ======================================= EXEC SPEMP1_1 @DEPTNO = 20 THE DEFAULT VALUE OF @DEPTNO WILL BE OVERWRITTEN AND DEFAULT VALUES OF @JOB WILL BE USED ========================================== HAVING SOME MANDATORY ARGUMENT AND SOME OPTIONAL ARGUMENTS ============================== CREATE PROC SPEMP1_1309 @DEPTNO INT, @JOB VARCHAR(20) = 'SALESMAN' AS SELECT * FROM EMP1 WHERE DEPT = @DEPTNO AND JOB = @JOB; EXEC SPEMP1_1309 30 => ITS A GOOD PRACTISE TO HAVE DEFAULT OR OPTIONAL ARGUMENTS AT LAST WHILE DECLARING PARAMTERS.... CREATE PROC SPEMP1_32 @DEPTNO INT, -- MANDATORY @MGR INT, --MANDATORY @JOB VARCHAR(20) = 'SALESMAN', --OPTIONAL @COMM MONEY = 500 --OPTIONAL AS SELECT * FROM EMP1 WHERE DEPT = @DEPTNO AND JOB = @JOB; EXEC SPEMP1_1 10,1123----- EXEC SPEMP1_1 @MGR = 1123, @DEPTNO = 10 ========================================================= GETTING THE CODE OF STORED PROCEDURE USING SYSTEM STORED PROCEDURE: EXEC SP_HELPTEXT SPEMP1_1 The text for object 'SPEMP1_1' is encrypted. Completion time: 2024-09-13T10:41:32.5032200+05:30 WHERE SP_HELPTEXT IS SYSTEM STORED PROCEDURE AND SPEMP1_1 IS USER DEFINED STORED PROCEDURE AND SP_HELPTEXT CAN BE USED TO SEE THE CODE OF VIEW,STORED PROC, USER DEFINED FUNCTIONS ETC =========================================== ENCRYPTING THE EXISTING STORED PROCEDURE ALTER PROC SPEMP1_1 @JOB VARCHAR(20) = 'SALESMAN', --OPTIONAL @DEPTNO INT WITH ENCRYPTION AS SELECT * FROM EMP1 WHERE DEPT = @DEPTNO AND JOB = @JOB; ========================================= ENCRYPTING THE STORED PROCEDURE WHILE CREATING STORED PROC CREATE PROC SPEMP1_1 @JOB VARCHAR(20) = 'SALESMAN', --OPTIONAL @DEPTNO INT WITH ENCRYPTION AS SELECT * FROM EMP1 WHERE DEPTNO = @DEPTNO AND JOB = @JOB; ================================================================================================================================================================== --------------------------------------------------------------------------------16-9-2024 ================================================================================================================================================================== WE WANT TO PASS OR RETURN THE VALUE/VALUES TO CALLING PROGRAM 1. RETURN STATEMENT -- IT CAN RETURN ONLY SINGLE INTEGER VALUE 2. OUTPUT PARAMETER --- IT CAN RETURN MULTIPLE VALUES OF ANY DATA TYPE 1. RETURN STATEMENT: BY DEFAULT WHEN WE EXECUTE A STORED PROCEDURE THEN IT RETURNS AN INTEGER VALUE AND THIS VALUE INDICATES EXECUTION STATUS OF STORED PROCEDURE. THE VALUE 0 INDICATES THE PROCEDURE IS COMPLETED SUCCESFULLY AND NON ZERO VALUE INDICATES AN ERROR. => TO USE THE RETURN VALUE IN THE CALLING PROGRAM, YOU MUST DECLARE A VARIABLE TO STORE ITS VALUE. => THE RETURN STATEMENT IMMEDIATELY EXITS THE PROCEDURE AND RETURNS AN OPTIONAL INTEGER VALUE TO THE CALLING PROGRAM. IF YOU DONT SPECIFY A VALUE IN THIS STATEMENT THE RETURN VALUE IS ZERO. CREATE PROC SPEMP1_3 AS SELECT * FROM EMP1 WHERE JOB = 'CLERK' DECLARE @T INT EXEC @T = SPEMP1_3 PRINT @T =============================== => RETURN THE EMPNO OF THE EMPLOYEE WHO IS CEO OF ORGANIZATION CREATE PROC SPEMP1_4 AS DECLARE @E INT SELECT @E= EMPNO FROM EMP1 WHERE JOB = 'CEO' RETURN @E DECLARE @EMPNO INT EXEC @EMPNO = SPEMP1_4 PRINT @EMPNO ============================================ 2. OUTPUT PARAMETER: OUTPUT PARAMTERS STORE VALUES THAT ARE PASSED BACK TO THE CALLING PROGRAM. WE IDENTIFY THE OUTPUT PARAMTERS BY OUTPUT KEYWORD AFTER THE PARAMETER NAME AND DATA TYPE. WHEN WE WANT TO RETURN SINGLE INTEGER VALUE--- BETTER TO GO WITH RETURN STATEMENT BUT WHEN WE WANT TO RETURN MULTIPLE VALUES OR VALUES OF DIFFERENT DATA TYPES THEN CHOOSE OUTPUT PARAMETER. => TO USE AN OUTPUT PARAMETER IN THE CALLING PROGRAM, YOU MUST DECLARE A VARIABLE TO STORE ITS VALUE. => RETURN THE EMPNO OF THE EMPLOYEE WHO IS CEO OF ORGANIZATION ALTER PROC SPEMP1_5 @DEPT INT OUTPUT AS SELECT @DEPT= DEPT FROM EMP1 WHERE JOB = 'PRESIDENT' SELECT * FROM EMP1 DECLARE @DEPTNO INT EXEC SPEMP1_5 @DEPTNO OUTPUT print @DEPTNO ================================== WE WANT TO TAKE JOB FROM CALLING PROGRAM AND RETURN CORRESPODING DEPTNO TO THE CALLING PROGRAM CREATE PROC SPEMP1_6 @DEPT INT OUTPUT, @JOB VARCHAR(20) AS SELECT @DEPT = DEPTNO FROM EMP1 WHERE JOB = @JOB EXECUTION USING POSITION: DECLARE @D INT EXEC SPEMP1_6 @D OUTPUT, 'PRESIDENT' SELECT @D PRINT @D EXECUTION USING NAME: DECLARE @D INT EXEC SPEMP1_6 @JOB = 'CEO',@DEPT = @D OUTPUT SELECT @D ============================================================================= ARCHIVED TABLES: CREATE A STORED PROCEDURE TO DELETE RECORD FROM A TABLE AND SAVE THE DELETED RECORDS IN ANOTHER TABLE SO THAT ANY TIME WRONG DATA IS DELETED WE CAN RECOVER. 1. DELETE THE RECORD A FROM A TABLE 1 2. INSERT THE DELETED RECORD INTO TABLE 2 SELECT * INTO ARCHIVE FROM EMP1 WHERE 1=2 CREATE PROC SPDEL_EMP1 @ENO INT AS INSERT INTO ARCHIVE SELECT * FROM EMP1 WHERE EMPNO = @ENO DELETE FROM EMP1 WHERE EMPNO = @ENO EXEC SPDEL_EMP1 109 ======================================= CREATE A PROCEDURE TO INCREMENT SPECIFIC EMPLOYEE SALARY BY A SPECIFIC AMOUNT IN EMP1 TABLE CREATE PROC SP_INC @ENO INT, @AMT MONEY AS UPDATE EMP1 SET SAL = SAL + @AMT WHERE EMPNO = @ENO EXEC SP_INC 107,500 ======================================= CREATE A PROCEDURE TO INCREMENT SPECIFIC EMPLOYEE SALARY BY A SPECIFIC AMOUNT IN EMP1 TABLE AND SEND THE UPDATED SAL TO CALLING PROGRAM CREATE PROC SP_INC1 @ENO INT, @AMT MONEY, @SAL MONEY OUTPUT AS UPDATE EMP1 SET SAL = SAL + @AMT WHERE EMPNO = @ENO SELECT @SAL = SAL FROM EMP1 WHERE EMPNO = @ENO DECLARE @S MONEY EXEC SP_INC1 107,1000,@S OUTPUT PRINT @S ============================================================================= CREATE A PROCEDURE FOR MONEY WITHDRAWL ACCOUNTS ACN ACT BAL 100 S 10000 101 S 20000 102 S 5000 CREATE PROC DEBIT @ACNO INT, @AMT MONEY, @NEWBAL MONEY OUTPUT AS DECLARE @BAL MONEY, @ERRMSG VARCHAR(100) BEGIN TRY IF NOT EXISTS(SELECT * FROM ACCOUNTS WHERE ACN = @ACNO) RAISERROR('ACCOUNT DOES NOT EXISTS', 15,1) SELECT @BAL = BAL FROM ACCOUNTS WHERE ACN = @ACNO IF @AMT>@BAL RAISERROR('INSUFFICIENT BALANCE',15,1) UPDATE ACCOUNTS SET BAL = BAL - @AMT WHERE ACN = @ACNO SELECT @NEWBAL = BAL FROM ACCOUNTS WHERE ACN = @ACNO END TRY BEGIN CATCH SET @ERRMSG = ERROR_MESSAGE() RAISERROR(@ERRMSG,15,1) END CATCH => WE CAN CREATE STORED PROCEDURE WHICH IS REFERENCING SOME TABLES EVEN WITHOUT EXISTENCE OF TABLES IN DB. BECAUSE IT USES DEFERRED NAME RESOLUTION. CREATE TABLE ACCOUNTS (ACN INT, ACT CHAR(1), BAL MONEY); INSERT INTO ACCOUNTS VALUES(100,'S',10000),(101,'S',20000),(102,'S',5000) DECLARE @B MONEY EXEC DEBIT 100, 1000,@B OUTPUT PRINT @B DROP THE PROCEDURE: ------------------------ DROP PROCEDURE PROCEDURENAME DROP PROC SPEMP1 ============================================================ TEMPORARY STORED PROCEDURES =>THEY ARE SAVED IN TEMPDB LOCAL STORED PROC -- WILL HAVE NAME STARTING WITH # GLOBAL STORED PROC -- WILL HAVE NAMES STARRTING WITH ## LOCAL STORED PROC =============== CREATE PROC #SPTEMP1 @ENO INT AS SELECT EMPNO, ENAME, SAL FROM EMP1 WHERE EMPNO = @ENO GO GLOBAL STORED PROC =============== CREATE PROC ##SPTEMP2 @ENO INT AS SELECT EMPNO, ENAME, SAL FROM EMP1 WHERE EMPNO = @ENO GO