1 / 9

Stored Procedures

Stored Procedures. Why Stored Procedures?. Modular, language-independent programming If stored on DB, then it can be called/used from any client program Faster execution of SQL code Pre-Optimized Pre-Compiled Stored in memory cache Reduce network traffic

rue
Download Presentation

Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Stored Procedures

  2. Why Stored Procedures? • Modular, language-independent programming • If stored on DB, then it can be called/used from any client program • Faster execution of SQL code • Pre-Optimized • Pre-Compiled • Stored in memory cache • Reduce network traffic • Send a small ‘Exec long_proc’ command instead of 100 lines of SQL • Security • Can grant permissions to procedures just like tables, views Source: http://msdn.microsoft.com/en-us/library/aa214299%28v=SQL.80%29.aspx#sql:stored_procedure

  3. Stored Procedures in SQL Server • Non-standard form • Syntax different from other DB’s, and what you see in book • Start: GO • CREATE PROCEDURE <Name> • End: • GO • Cannot have anything besides the procedure inside the GO statement

  4. Syntax CREATE PROCEDURE [ owner. ] procedure_name[ ; number ]     [ { @parameter data_type}[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] AS sql_statement [ ...n ] • Owner: username in system • Number: way of categorizing a set of procedures • @parameter input and OUTPUT variables • sql_statement: Any code in Microsoft’s T-SQL language • Includes control structures and variables Source: http://msdn.microsoft.com/en-us/library/aa258259%28v=SQL.80%29.aspx

  5. Static Example CREATE PROCEDURE inst_vocal_tracks AS select A.album_name FROM Albums A WHERE EXISTS (select * from tracks T WHERE T.album_id = A.album_id AND T.instrumental = 'y') AND EXISTS (select * from tracks T WHERE T.album_id = A.album_id AND T.vocal = 'y') GO EXEC inst_vocal_tracks GO

  6. Dynamic Example CREATE PROCEDURE emps_by_title @titlenamevarchar(10) AS select E.Lname, E.Fname FROM Employee E, Position P WHERE E.positionid = P.positionid AND P.PosDesc = @titlename GO EXEC emps_by_title @titlename = ‘Accountant’ GO

  7. OUTPUT EXAMPLE! CREATE PROCEDURE tracks_for_album @alb_namevarchar(80), @num_tracks integer OUTPUT AS SELECT @num_tracks = COUNT(track_num) FROM Albums A, Tracks T WHERE A.album_id = T.album_id AND A.album_name = @alb_name GO

  8. Retrieving OUTPUT --declare some variables DECLARE @output_count integer DECLARE @input_namevarchar(80) = 'The Bagpipes and Drums of Scotland' --run the procedure exec tracks_for_album @input_name, @output_count OUTPUT print 'The number of tracks in ' + @input_name + ' is: ' + RTRIM(CAST(@output_count AS varchar(20))) go

  9. What We Haven’t Covered • Most major DB systems have SQL procedures, but also allow you to write stored procedures in one or more full-featured programming languages (e.g. Java, C++, C#, C, etc.) • You might choose to learn how to do this if your DB application area is: • Science • E-Commerce • Banking • Others? • Careful not to write too many stored procedures! • Vendor specific, may not transfer if your company switches products.

More Related