ITE 370: SQL Stored Procedures

1. ITE 370: SQL Stored Procedures

2. 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

3. 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 (Results are stored temporarily)

4. 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

5. Stored Procedure Syntax – SQL Server CREATE PROC [ EDURE ] procedure_name     [ { @parameter data_type } [ = default ] [ OUTPUT ]     ] [ ,...n ] AS sql_statement [ ...n ]

6. Example – Create a stored proc in SQL-Server • Write this in the SQL Server Management Studio Query Builder and run to create a new stored procedure: CREATE PROCEDURE GetStopsOnRoute @RouteIdint AS SELECT * FROM StopOnRoute WHERE RouteId=@RouteId

7. Same Example, More Detail CREATE PROCEDURE dbo.GetStopsOnRoute -- returns a chronological listing of bus stops on a given bus route @RouteIdint -- PK of a bus route to list AS BEGIN -- sproc -- retrieve information for all Stops on the given bus route, listed -- in chronological order SELECT RouteId, StopOnRouteId, Stop.StopId, Location, ElapsedTime, Outbound FROM StopOnRoute INNER JOIN Stop ON StopOnRoute.StopId=Stop.StopId WHERE RouteId=@RouteId ORDER BY ElapsedTime END -- sproc -- example call for RouteId=2 (written seperately in the query builder) -- EXEC dbo.GetStopsOnRoute 2 -- execute script GO

8. Stored Proc Example with Insert CREATE PROCEDURE dbo.InsertStop -- adds a new row to the table of bus stops @Location nvarchar(50), -- location of new bus stop to insert @Shelter bit, -- shelter of stop to insert; 1=has shelter, 0=not @Seating nvarchar(25) -- type of seating (bench, multi-seat) of stop AS BEGIN -- sproc INSERT INTO Stop (Location, Shelter, Seating) VALUES (@Location, @Shelter, @Seating) END -- sproc -- example call to insert a new bus stop row (written as separate query) -- EXEC dbo.InsertStop 'Ladd-Peebles Stadium', 1, 'Multi-Seat' -- verify that row was added by searching for it -- SELECT * FROM Stop WHERE Location LIKE 'Ladd%' -- note the % is SQL-Server wild-card -- execute the script GO

9. Example: Calling a stored proc

10. 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

11. Declaring Local Variables • Syntax: DECLARE     {{ @local_variable data_type }             } [ ,...n] • Example: DECLARE @StopCount int, @LastName nvarchar(20)

12. Assigning Values to Local Variables • Syntax: SET { { @local_variable = expression } } • Examples: SET @LastName = ‘Manning’ SET @StopCount = 42 SET @HR = (SELECT Sum(HR) FROM Player)

13. Stored Procs with Output Parameter ALTER PROC dbo.GetNumOfStopsOnRoute @RouteId int, @StopCount int OUTPUT AS SET @StopCount = (SELECT Count(*) AS Stops FROM StopOnRoute WHERE RouteId=@RouteId) -- example call to retrieve number of stops on RouteId=1 /* DECLARE @NumOfStops int SET @NumOfStops = 0 EXEC dbo.GetNumOfStopsOnRoute 1, @StopCount = @NumOfStops OUTPUT SELECT @NumOfStops AS TotalStops */ GO