Transcript Stored Procedures

Task oriented processing
And Queries
How do I want my information?
• A customer should not be required to
remember
– An order number
– A product code
– An account code
• except for security purposes
Where is my…?
• A customer of Northwind wants to know
–
–
–
–
–
Where is the order I placed last week?
How much do I pay for Chai?
How much more does Chai cost than Chang?
What is the least expensive drink?
What is the fanciest (most expensive) cheese?
How do I manage …?
• A line manager wants to know
– Are there any products that are out of stock?
– Are there any customers on the record that have
never placed an order?
– Are there any employees who have never made
a sale?
How do I save …?
• A managing director wants to know
–
–
–
–
What is our best selling product?
What is our highest earning product?
Who is our highest earning employee?
Who is the employee who sells the most units?
Formulating queries
• Ask the question the user would ask
• Avoid code-dependent queries – go for
name-based or point and pick based queries.
• Once the query has been stated
– Consult the ERD
– Check the enquiry access path
– Try to fill the query using joins rather than
separate queries.
Stored Procedures
Saving your queries in your database
Simple Stored Procedures
• Create the query and check it in the Query Analyser.
• Using the Enterprise Manager, Add a new stored
procedure to your database.
• Name your Stored Procedure appropriately.
• Paste in your query, check syntax and click OK.
• Run your stored procedure from the query analyser
using ‘EXEC <SP_NAME>.
Using variables
• Variables can be used in SQL Server using
either the Query Analyser or Stored
Procedures.
• All variables must begin with the symbol @
• Variables must be given a data type:
• e.g.
Declare @cost numeric(6,2);
Declare @name varchar(20);
Using Variables
• Variables can be assigned values using Set:
Set @cost = 0;
Set @cost = @cost * 1.1;
• Or using Select:
Declare @givenId numeric(7,0);
Set @givenId = 1234567
Select @name = suppliername from Supplier where
SupplierId = @givenId;
Parameters in Stored Procedures
• Parameters can be passed to stored procedures by
reference.
• If the stored procedure is expected to return a
significant value, then it should be explicitly
marked as an output parameter.
• To use parameters:
– Define the formal parameters in the Stored Procedure.
– Define the actual parameters and give them values in
the Query Analyser.