Stored procedures
This presentation is the property of its rightful owner.
Sponsored Links
1 / 9

Stored Procedures PowerPoint PPT Presentation


  • 69 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Stored procedures

Stored Procedures


Why 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

    • 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

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

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

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

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

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

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

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.


  • Login