SQL Stored Procedures

SQL Stored Procedures Jeffrey P. Landry University of South Alabama University of South Alabama School of CIS

Stored Procedures • A stored procedure is • a collection of SQL statements saved under a name and processed by the database server as a unit • Stored procedures • are precompiled and ready for later use • can take and return user-supplied parameters • may contain control-of-flow statements (conditionals, loops) • are created, modified, called, tested using SQL-Server University of South Alabama School of CIS

Advantages of Stored Procedures • Reuse • write once, call many • can be shared by multiple users of db • Convenient means of abstraction • Easier to write than SQL embedded within VB • Centralizes access to data • More secure means of data access • Fast execution • precompiled SQL statements • cached results University of South Alabama School of CIS

Disadvantages of Stored Procedures • Extra layer of hidden complexity on top of SQL statements • Can be used to restrict application and user access to data • Programming environment not as advanced as modern, visual IDEs • Fewer advanced code writing, testing, debugging features available in SQL-Server • No search and replace University of South Alabama School of CIS

Stored Procedure Syntax – SQL Server CREATE PROC [ EDURE ] procedure_name [ { @parameter data_type } [ = default ] [ OUTPUT ] ] [ ,...n ] AS sql_statement [ ...n ] University of South Alabama School of CIS

Example – Create a stored proc in SQL-Server • Write this in the SQL Query Analyzer and run to create a new stored procedure: CREATE PROCEDURE GetEmployeesInDept @Dept int AS SELECT * FROM Employee WHERE DeptId=@Dept University of South Alabama School of CIS

Stored Proc Example with Insert CREATE PROCEDURE dbo.InsertEmployee -- adds a new row to the table of employees @First varchar(15), -- employee's first name @Last nvarchar(15), -- employee's last name @Hire DateTime, -- date that employee was hired by the company @PayRate decimal(8,2) -- employee's hourly rate of pay AS BEGIN -- sproc INSERT INTO Employee (FirstName, LastName, HireDate, PayRate) VALUES (@First, @Last, @Hire, @PayRate) END -- sproc -- example call to insert a new employee row -- EXEC dbo.InsertEmployee 'Kelly', 'Reed', '4/8/2005', 17.50 -- verify that row was added by searching for it -- SELECT * FROM Employee WHERE LastName LIKE 'Re%' -- note the % wild-card -- execute the script GO University of South Alabama School of CIS

Example: Calling a stored proc University of South Alabama School of CIS

Modifying Stored Procs in SQL-Server • Use the ALTER PROCEDURE command • Instead of CREATE • Because the sproc already exists • Script stored under Properties • Paste into the Query Analyzer • Edit, run, debug, run • Remember to Refresh in Enterprise Manager • Scripts can be stored locally as text files with .sql default extension University of South Alabama School of CIS

Declaring Local Variables • Syntax: DECLARE {{ @local_variable data_type } } [ ,...n] • Example: DECLARE @ManagerId int, @LastName nvarchar(15) University of South Alabama School of CIS

Assigning Values to Local Variables • Syntax: SET { { @local_variable = expression } • Examples: SET @LastName = ‘Manning’ SET @ManagerId = 42 SET @AvgPay = (SELECT Avg(PayRate) FROM Employee) University of South Alabama School of CIS

Stored Procs with Output Parameter CREATE PROC dbo.GetNumOfEmployeesInDept @DeptId int, @EmployeeCount int OUTPUT AS SET @EmployeeCount = (SELECT Count(*) AS EmpCnt FROM Employee WHERE DeptId=@DeptId) -- example call to retrieve number of employees in DeptId=1 /* DECLARE @NumOfEmps int SET @NumOfEmps = 0 EXEC dbo.GetNumOfEmployeesInDept 1, @EmployeeCount = @NumOfEmps OUTPUT SELECT @NumOfEmps AS TotalEmps */ GO University of South Alabama School of CIS

SQL Stored Procedures