Vwcustomerorders as select oorderid ccompanyname from

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: o ON c.CustomerID = O.CustomerID ORDER BY o.OrderId Relational Databases View Usage Example SELECT * FROM vwCustomerOrders WHERE CompanyName = 'My Favorite Customer' OrderId CompanyName 101 My Favorite Customer 137 My Favorite Customer … 9 5/29/2013 5/29/2013 Relational Databases Stored Procedures A group of SQL statements that runs within the database Not part of SQL standard Provides greater performance Can control access to data Can accept parameters Can return data Output parameters Return values Result set Relational Databases Stored Procedure Example CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName 10 5/29/2013 5/29/2013 Relational Databases Stored Procedure Examples exec CustOrderHist 'alfki' ProductName Total Aniseed Syrup 6 Chartreuse verte 21 ... ... Relational Databases Stored Procedure Examples Use RETURN statement to return status 0 is default in SQL Server Can only be numeric RETURN 1 Use OUTPUT parameters to return results CREATE PROCEDURE MyProcedure @ReturnValue INT OUTPUT ... SELECT @ReturnValue = ColumnName FROM Table 11 5/29/2013 5/29/2013 Relational Databases Triggers Like stored procedures, triggers are code that runs within a database Not directly called by a user Executed when a specified data modification takes place (INSERT, UPDATE or DELETE) Enforces business rules FOR AFTER: trigger executes after triggering action completes FOR INSTEAD OF: trigger executes in place of triggering action Relational Databases Transactions Transaction: a sequence of SQL statements that constitute a logical unit of work 12 5/29/2013 5/29/2013 Outline Databases Relational Databases ADO.NET Overview ADO.NET Classes ADO.NET Overview Looking Back ODBC (Open Database Connectivity) Interoperability to a wide range of data...
View Full Document

Ask a homework question - tutors are online