ITE 370 SQL Stored Procedures 2232021 1 Stored

ITE 370: SQL Stored Procedures 2/23/2021 1

Stored Procedures n A stored procedure is n n 2/23/2021 a collection of SQL statements saved under a name and processed by the database server as a unit Stored procedures n are precompiled and ready for later use n can take and return user-supplied parameters n may contain control-of-flow statements (conditionals, loops) n are created, modified, called, tested using SQLServer 2

Advantages of Stored Procedures n Reuse n n n Convenient means of abstraction n n write once, call many can be shared by multiple users of db Easier to write than SQL embedded within VB Centralizes access to data More secure means of data access Fast execution n n 2/23/2021 precompiled SQL statements cached results (Results are stored temporarily) 3

Disadvantages of Stored Procedures n n n 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 n n 2/23/2021 Fewer advanced code writing, testing, debugging features available in SQL-Server No search and replace 4

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

Example – Create a stored proc in SQL-Server n Write this in the SQL Server Management Studio Query Builder and run to create a new stored procedure: CREATE PROCEDURE Get. Stops. On. Route @Route. Id int AS SELECT * FROM Stop. On. Route WHERE Route. Id=@Route. Id 2/23/2021 6

Same Example, More Detail CREATE PROCEDURE dbo. Get. Stops. On. Route -- returns a chronological listing of bus stops on a given bus route @Route. Id int -- 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 Route. Id, Stop. On. Route. Id, Stop. Id, Location, Elapsed. Time, Outbound FROM Stop. On. Route INNER JOIN Stop ON Stop. On. Route. Stop. Id=Stop. Id WHERE Route. Id=@Route. Id ORDER BY Elapsed. Time END -- sproc -- example call for Route. Id=2 (written seperately in the query builder) -- EXEC dbo. Get. Stops. On. Route 2 -- execute script GO 2/23/2021 7

Stored Proc Example with Insert CREATE PROCEDURE dbo. Insert. Stop -- 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. Insert. Stop '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 SQLServer wild-card -- execute the script GO 2/23/2021 8

Example: Calling a stored proc 2/23/2021 9

Modifying Stored Procs in SQLServer n Use the ALTER PROCEDURE command n n n n 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 2/23/2021 10

Declaring Local Variables n Syntax: DECLARE {{ @local_variable data_type } } [ , . . . n] Example: DECLARE @Stop. Count int, @Last. Name nvarchar(20) n 2/23/2021 11

Assigning Values to Local Variables Syntax: SET { { @local_variable = expression } } n n Examples: SET @Last. Name = ‘Manning’ SET @Stop. Count = 42 SET @HR = (SELECT Sum(HR) FROM Player) 2/23/2021 12

Stored Procs with Output Parameter ALTER PROC dbo. Get. Num. Of. Stops. On. Route @Route. Id int, @Stop. Count int OUTPUT AS SET @Stop. Count = (SELECT Count(*) AS Stops FROM Stop. On. Route WHERE Route. Id=@Route. Id) -- example call to retrieve number of stops on Route. Id=1 /* DECLARE @Num. Of. Stops int SET @Num. Of. Stops = 0 EXEC dbo. Get. Num. Of. Stops. On. Route 1, @Stop. Count = @Num. Of. Stops OUTPUT SELECT @Num. Of. Stops AS Total. Stops */ GO 2/23/2021 13