90 likes | 227 Views
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
E N D
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
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
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
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
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
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
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
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.