------------------------------- -- Annonymous Blocks ------------------------------- DECLARE @v_int int BEGIN SET @v_int = 5 PRINT @v_int END ------------------------------ -- Named Programs -- 1. Procedures -- 2. Functions -- 3. Triggers ------------------------------ ------------------------------ -- Procedures ------------------------------ USE Northwind GO -- CREATE CREATE PROCEDURE myfirstproc AS DECLARE @v_int int BEGIN SET @v_int = 5 PRINT @v_int END EXEC myfirstproc GO -- ALTER ALTER PROCEDURE myfirstproc AS DECLARE @v_int int BEGIN SET @v_int = 10 PRINT @v_int END EXEC myfirstproc -- DROP DROP PROCEDURE myfirstproc -- P1 GO -------------------------------------------------- --empName -------------------------------------------------- CREATE PROCEDURE empName AS DECLARE @v_name varchar(25) BEGIN SELECT @v_name = FirstName FROM employees WHERE employeeID = 5 PRINT @v_name END -- Procedure with parameters GO ALTER PROCEDURE empName (@p_empid int) AS DECLARE @v_name varchar(25) BEGIN SELECT @v_name = FirstName FROM employees WHERE employeeID = @p_empid PRINT @v_name END EXEC empName 5 EXEC empName 6 -------------------------------------------------- -- emp_for_raise_proc -------------------------------------------------- SELECT * FROM employees GO CREATE PROCEDURE emp_for_raise_proc (@p_empid int) AS DECLARE @v_empcity varchar(25), @v_empname varchar(25) BEGIN SELECT @v_empcity = city , @v_empname = lastName FROM employees WHERE employeeID = @p_empid IF @v_empcity = 'London' PRINT 'This employee is up for a raise (go london !) --> ' + @v_empname ELSE PRINT 'This employees is not up for a raise -- > ' + @v_empname END EXEC emp_for_raise_proc 5 EXEC emp_for_raise_proc 1 ------------------------------------------ -- P2 ------------------------------------------ GO ALTER PROCEDURE check_price_proc (@p_productID int) AS DECLARE @v_unitPrice int , @v_productName varchar(25) BEGIN SELECT @v_unitPrice = unitPrice , @v_productName = productName FROM products WHERE productID = @p_productID PRINT 'Current unitPrice is : ' + CAST(@v_unitPrice AS VARCHAR) + ' (' + @v_productName + ')' IF @v_unitPrice > 50 PRINT 'Greater than 50' ELSE IF @v_unitPrice = 50 PRINT 'Equal to 50' ELSE BEGIN PRINT 'Less than 50' UPDATE products SET unitPrice = unitPrice * 1.1 WHERE productID = @p_productID -- Print new price SELECT @v_unitPrice = unitPrice , @v_productName = productName FROM products WHERE productID = @p_productID PRINT 'Current unitPrice is : ' + CAST(@v_unitPrice AS VARCHAR) + ' (' + @v_productName + ')' END END EXEC check_price_proc 13 SELECT productID FROM products WHERE unitPrice < 50 ----------------------------------- -- Procedure with cursor ----------------------------------- GO ALTER PROCEDURE price_range_proc (@p_minprice int , @p_maxprice int ) AS DECLARE products_range_cur CURSOR FOR SELECT productName , unitPrice FROM products WHERE unitPrice BETWEEN @p_minprice AND @p_maxprice DECLARE @v_unitPrice int , @v_productName varchar(25) BEGIN OPEN products_range_cur FETCH NEXT FROM products_range_cur INTO @v_productName , @v_unitPrice WHILE @@FETCH_STATUS = 0 BEGIN PRINT @v_productName + ' ' + CAST (@v_unitPrice AS VARCHAR) FETCH NEXT FROM products_range_cur INTO @v_productName , @v_unitPrice END CLOSE products_range_cur DEALLOCATE products_range_cur END EXEC price_range_proc 50 , 51 ----------------------------------- -- OUT PARAMETER ----------------------------------- ALTER PROCEDURE empName (@p_empID int, @p_firstName varchar(25) OUTPUT) AS DECLARE @v_name varchar(25) BEGIN SELECT @v_name = FirstName FROM employees WHERE employeeID = @p_empID --PRINT @v_name SET @p_firstName = @v_name END DECLARE @v_catch_name varchar(25) BEGIN EXEC empName 5, @v_catch_name OUTPUT PRINT @v_catch_name END -- P5/6 ALTER PROCEDURE procName_proc (@p_productID int, @p_productName varchar(25) OUTPUT , @p_status int OUTPUT ) AS DECLARE @v_name varchar(25) , @v_price int BEGIN SELECT @v_name = productName , @v_price = unitPrice FROM products WHERE ProductID = @p_productID --PRINT @v_name SET @p_productName = @v_name IF @v_price > 50 SET @p_status = 1 ELSE SET @p_status = 0 END DECLARE @v_catch_name varchar(25) , @v_catch_status int BEGIN EXEC procName_proc 5, @v_catch_name OUTPUT , @v_catch_status OUTPUT PRINT @v_catch_name PRINT @v_catch_status END ----------------------------------- -- Functions ----------------------------------- GO CREATE FUNCTION dbo.add_maam (@num_to_plus_maam MONEY) RETURNS MONEY BEGIN RETURN @num_to_plus_maam*1.18 END SELECT dbo.add_maam(100) SELECT productName , unitPrice ,dbo.add_maam(unitPrice) FROM products