Programmatic part of the SQL server is called Stored Procedure. This may consists of SQL and non SQL statements.
Eg:1
DECLARE @A INT
DECLARE @B INT
DECLARE @C INT
SET @A=100
SET @B=200
BEGIN
SET @C = @A+ @B
SELECT ‘Sum Is’, @C
END
O/P - Sum Is 300
Update a Table
Eg:2
DECLARE @N VARCHAR (20), @EN INT
SET @N=’UNNI’
SET @EN=1
BEGIN
SELECT * FROM EMPLOYEES WHRE ENAME = @N
UPDATE EMPLOYEES SET SAL =SAL+500 WHERE ENO=@EN
SELECT ‘SALARY UPDATED’
END
User Defined Procedure
Procedure can be defined as a Sub Program which performs an action, this can be called from other Programs. (JAVA, C#, VB, C++)
Procedure may or may not return Value.
Eg:3
CREATE PROCEDURE PR1 @N1 INT, @N2 INT
AS
DECLARE @R INT
SET @R = @N1+ @N2
SELECT ‘Sum Is’, @R
Calling Program
EXEC PR1 250,750
O/P - Sum Is 1000
Procedure that Returns the Value
Eg:4
CREATE A PROCEDURE PR2 @N1 INT, @N2 INT
AS
DECLARE @R INT
SET @R=@N1+@N2
SELECT ‘Sum Is’ ,@R
RETURN @R
Calling Program
DECLARE @ TOTAL INT
EXEC @TOTAL = PR2 250, 750
SELECT @TOTAL/2 AS ‘Averate of Two Numbers’
Eg 5
CREATE PROCEDURE PR3 @EN INT
AS
DECLARE @SL INT, @COMM INT
BEGIN
SELECT @SL=SAL FROM EMPLOYEES WHERE ENO=@EN
SET @COMM=@SL *.1
SELECT ‘Commission Is’,@COMM
END
Calling Program
EXEC PR3 5
Note: How it works
· In Calling Program, we input a value to procedure (This Value must be in the table)
· This Value stored in PR3 variable @EN
· Selects the @EN value from the table Employees (SELECT FROM EMPLOYEES WHERE ENO=@EN)
· It stores the variable @SL
· @SL multiplies to .1 & it stores to @COMM
· @COMM displays the output with ‘Commission is’
Dropping a Procedure
DROP PROCEDURE
EG: DROP PROCEDURE PR1
.png)
.png)
