You can then run this stored procedure in

You can then run this stored procedure in your SQL Analyzer with the expression EXEC QUERYSALES 2004 ,200000 This statement passes the values of the two parameters into the stored procedure. It tells SQL Server to run the QUERYSALES stored procedure with @year = 2004 and @threshold= $200,000. Impressed yet? By now, the power of the SQL Server database management system a system that puts the power of the relational database at your fingertips should be pretty obvious. Even better news is the fact that you can harness this database access power and use it to retrieve information from a database for the kinds of reports you would want to build with Microsoft Reporting Services. In this chapter, you began your journey to construct reports. Knowing the basics of SQL and stored procedures serves as a good foundation for creating powerful reports using Microsoft Reporting Services. Chapter 2: Retrieving Data from a Database 45

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Using the last SQL query in the earlier

going to be a dollar amount, so define the @threshold parameter data type as money. Your stored procedure code should then look like this: CREATE PROCEDURE TestProcedure Add the parameters for the function here @year char(4) , @threshold money AS BEGIN 5. Enter the SQL query for the procedure. The SQL statement substitutes @year for the year 2004 and @threshold for the SubTotal amount in the WHERE clause of the last query in the earlier GROUP BY for subtotals section. The statement should be as follows: SELECT s.AccountNumber as Account , count(*) as # Orders , SUM(SubTotal) as Total Order FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and Year(OrderDate) = @Year GROUP BY s.AccountNumber HAVING SUM(SubTotal) > @Threshold ORDER BY SUM(SubTotal) desc 6. Check your stored procedure by using the Checkmark button on the SQL Editor toolbar. You ll receive error messages if there are any issues with the statement you entered which should not happen if you follow these instructions. 7. Save the stored procedure by right-clicking anywhere in the editor window where you have built the SQL statement and choosing Execute Item from the pop-up menu that appears. You have now created a new stored procedure. The final procedure code should look like this: CREATE PROCEDURE TestProcedure — Add the parameters for the function here @year char(4) , @threshold money AS BEGIN SELECT s.AccountNumber as Account , count(*) as # Orders , SUM(SubTotal) as Total Order FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and Year(OrderDate) = @Year GROUP BY s.AccountNumber HAVING SUM(SubTotal) > @Threshold ORDER BY SUM(SubTotal) desc END GO 44 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Using the last SQL query in the earlier

Using the last SQL query in the earlier section, GROUP BY for subtotals, you can create a stored procedure that meets all necessary requirements doing the following: 1. With Management Studio open, find your database in the Object Explorer window and click on the folder named Programmability to expand within your database folder. All the subfolders underneath Programmability are displayed. 2. Right-click the Stored Procedure subfolder in the tree. 3. Choose New Stored Procedure from the pop-up menu that appears. A template for creating a stored procedure in the Query Editor window appears. Delete all the text before the PROCEDURE statement and enter the procedure name as TestProcedure. 4. Define the parameters for the stored procedure. For this example, you want to define two variables: Year and Threshold value for sales. Enter the parameter name as @year. Because year is a four-digit character, define the data type to be char(4). Threshold is Figure 2-4: SQL statement and the result set displayed in Query Analyzer. Chapter 2: Retrieving Data from a Database 43

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Note: Sharper eyes will have noticed a new

When used with the AdventureWorks sample database, this query returns seven rows indicating that for the year 2004, seven customers had more than $200,000 in sales. As a final illustration, Figure 2-4 shows a slightly revised SQL statement to retrieve the Store Name and include it in the result set called up by our SQL query. Notice that I had to include a join from the Customer table to the Store table in order to return the Store Name info instead of the AccountNumber in the SalesOrderHeader. This was accomplished by adding a condition to the WHERE clause that s.CustomerID = c.CustomerID. All this works because CustomerID is a key in the Customer table and a foreign key in the Store table. Respecting the stored procedures All you power users out there are going to love this one. It s time to talk about the last concept in my introductory tour: the stored procedure. The stored procedure is a query that is compiled as code on the server. Nice, but nothing to write home about, you say? How about if I told you that the execution plan for the query within the stored procedure is also fixed when the procedure is stored? The execution plan refers to the method that the SQL Query Optimizer will use to retrieve the data. For stored procedures, the query execution plan is stored and executes quickly. Queries that are not in stored procedures are evaluated at run-time to generate a query execution plan and are then executed, introducing delays in the processing. The stored procedure can be used in a report and executes faster than normal SQL because it was already compiled when it was created. The database engine does not have to evaluate it as closely as SQL code that has not been stored as a procedure. Note: I refer to SQL Server syntax in this section. Other database management systems have their own quirks in terms of syntax and conventions, so if you go the non-SQL Server route, you re on your own. Another nice thing about stored procedures is that you can pass parameters the specific values of variables defined in the stored procedure into them. (Parameters are the variables whose values can be changed upon each execution of the SQL expressions within the stored procedure.) For example, you might want to write a procedure that has a Year parameter and a Minimum Threshold parameter as part of your investigation of account sales. To do this, you would need to declare variables @year and @amount and reference these variables in the stored procedure. (The @ symbol is what you use to identify a variable in a stored procedure.) 42 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Note: Sharper eyes will have noticed a new

Note: Sharper eyes will have noticed a new keyword the HAVING keyword used here with the aggregate sum(SubTotal). Why does one have to have HAVING? Well, the only way to filter an aggregate function (a function like SUM, MAX, or MIN) is to use it in a HAVING expression. You cannot use an aggregate function within a WHERE expression. As a final note, the HAVING expression comes after the GROUP BY expression and before any ORDER BY expression. One final example, and you ll have completed a great SQL workout! Just imagine that you want to specify the year for which you want to analyze sales. Instead of specifying in your WHERE clause a start date (>= January 1 of the year) and an end date (< = December 31 of the year) in the query, you can use a date function on the OrderDate field. Date functions, to no one s surprise, are used to display information about dates. Specifically, you can use the YEAR function to return an integer that represents the year part of a specified date. The function YEAR(OrderDate) = 2004 in the WHERE clause specifies that you want orders where the order data was sometime in 2004. The resulting query is SELECT s.AccountNumber as Account , count(*) as # Orders , SUM(SubTotal) as Total Order FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and Year(OrderDate) = 2004 GROUP BY s.AccountNumber HAVING sum(SubTotal) >200000 ORDER BY sum(SubTotal) desc Chapter 2: Retrieving Data from a Database 41 Creating views in the database Bear in mind that it might be easier to request that a database administrator create a view to query from. A view is a virtual table that represents the data in one or more tables in an alternative way. A view can simplify the tables and bring them together to make writing SQL much easier than working with the original underlying tables. This approach is a design consideration that can greatly simplify the work of getting information out of the database. A view can reference a maximum of 1,024 columns and can filter unwanted data or embed some business rules in how tables should be combined for reporting. You can also UNION together tables that can be created for a specific year so you can query data for several years in a single view. (The UNION command is a way of combining the results of two similar queries in the same result set.) The columns need to match in terms of number and data type between the two SQL statements being combined. The UNION command should be considered a way to make querying information easier within a complex or confusing database model.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Note that I started a new line in

Notice that for this example, you use an aggregate function (SUM) in the SELECT expression. You use the SUM function to indicate that you re interested in getting the sum of the sales SubTotal based on the selection criteria of the query (which in this case, was a restriction only on the OrderDate). When you use an aggregate function like SUM, you also need to have a GROUP BY expression to indicate that you still want to group by the other SELECT variables that don t use an aggregate function. You could specify multiple GROUP BY variables in the query, which would indicate where subtotals would occur in the result set. The SQL query here returns a result set that groups a sum of the sales SubTotal amount by account number and then orders these groupings from highest to lowest so you can determine which accounts represent the most revenue to the company. Note that the GROUP BY expression precedes the ORDER BY expression. If you forget the order of these expressions, your SQL analyzer tool reports a syntax error and lists the keyword where the error occurs. In this case, the error is at the GROUP BY keyword SQL s little signal to you to get the expressions in the right order! HAVING for filtering Want to try for more? How about an example that tells you how many orders there were for the account that ended up being the total sales winner in the query. You need to use the COUNT function, which returns the number of rows satisfying each grouping of the result set. To make it even more interesting, go ahead and add just a little new wrinkle and specify the column names to be shown on the results set listing. This can be done by using the AS keyword after the variable in the SELECT expression, followed by the title to be displayed in quotes. Finally, for the pi ce de r sistance, set it up so that SQL looks at orders only where the sales SubTotal is greater than $200,000. When you have all your SQL ducks in a row, the resulting query should look like this: SELECT s.AccountNumber as Account , count(*) as # Orders , SUM(SubTotal) as Total Order FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and OrderDate >= 1/1/2004 GROUP BY s.AccountNumber HAVING SUM(SubTotal) >200000 ORDER BY SUM(SubTotal) desc Now I ll use another language to describe the effect of this query voil ! (That s French, Morticia!) 40 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Note that I started a new line in

Note that I started a new line in the WHERE clause for each condition to enhance the full statement s readability. Also, I need to enter single quotes around that date field value so that the data field OrderDate can be evaluated with a literal value of the date. SQL Server only recognizes date and time values enclosed in single quotes. This query returns 13,951 rows indicating the number of orders taken with order dates on or after January 1, 2004. ORDER in the court Seeing all orders for a year in a particular order might prove useful to you (or to some higher-up in your company). One common way to look at sales especially when it comes to handing out bonuses is to look from highest to lowest. This sort order is a descending sort order. The way to specify the sort variable and direction on your SQL command is to use the ORDER BY clause. To see the results from highest to lowest subtotal in the sample AdventureWorks database, use the following syntax: SELECT s.AccountNumber, SubTotal FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and OrderDate >= 1/1/2004 ORDER BY SubTotal desc The result set will be ordered from highest to lowest value of column SubTotal for all account numbers in the SalesOrderHeader table. Note that the desc keyword is an abbreviation for descending. If you want an ascending sort, use the keyword asc. As always, the FROM and WHERE clauses make liberal use of aliases, as spelled out in the WHERE you join the party section, earlier in this chapter. GROUP BY for subtotals Variety is the spice of life, so go ahead and group your orders by the AccountNumber field. The GROUP BY expression specifies the groups into which output rows are to be placed. The SQL expression you ll write looks like this: SELECT s.AccountNumber, SUM(SubTotal) FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and OrderDate >= 1/1/2004 GROUP BY s.AccountNumber ORDER BY SUM(SubTotal) desc Chapter 2: Retrieving Data from a Database 39

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

If you need some more support on writing

logical and is logical but having a single name for what seem to be two separate items in two distinct tables is going to confuse SQL s computer brain further down the road if you don t intervene now. To that end, do exactly what I did in the preceding query and provide an alias in the FROM clause for each of the tables that you want to work with. (I usually use the first initial, or at most a two-letter alias, for my tables.) As long as you define the alias for each table in the FROM clause and use the alias in the rest of the SQL query to uniquely show where columns are sourced from, SQL Server will know how to process your query. The alias saves you from having to enter fully qualified names for the columns in your query. In this way, you can simply refer to the non-unique column customerID but prefix it with the alias of the table in which to find this column. The alias and column name combination must be a unique reference in order that the SQL can be understood and executed by the database. (Of course, when you get to the WHERE clause in your query, you ll have to use the same aliases referenced in the FROM clause; only then can the WHERE clause look in the right place for what you want.) If you want to use an alias that has spaces in it, you must use double quotation marks around the alias name. It is best to use text letter(s) for the aliases because most symbols produce SQL error messages. You should also use the alias concept when you want to see a non-unique column (such as customerID) in the result set. For example, to retrieve customerID in the result set, if you don t preface the column name with an alias, you receive the system-generated error message: Ambiguous column name CustomerID To resolve this issue, use the alias with the non-unique column name in the SELECT statement. It does not make a difference which table alias you use in the following example: SELECT c.customerID, s.AccountNumber, OrderDate, SubTotal FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID The WHERE clause also enables you to filter your query. For example, if you are interested in orders in 2004 listed in the sample AdventureWorks database, you could issue the following query: SELECT s.AccountNumber, OrderDate, SubTotal FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID and OrderDate >= 1/1/2004 38 Part I: Just the Basics

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

If you need some more support on writing

If you need some more support on writing SQL queries from within Query Analyzer, click the Help menu and select the Transact-SQL Help topic from the menu. Search for SELECT, which is a good place to start with more detail on syntax. Transact-SQL is the SQL syntax that SQL Server 2005 understands. Other variations of SQL have been developed for other database engines. WHERE you join the party If you would like to retrieve information from columns of multiple tables, you first need to join the two tables. You do this by referring to both tables in the FROM clause of your query and then adding a WHERE clause to stipulate what criteria SQL should use to find which columns to search. And what might that criteria be? Easy. By setting the primary key of the first table to the foreign key of the corresponding related table, you tell SQL that the two tables are linked and are therefore fair game for whatever search you undertake. (For more on primary and foreign keys, see the earlier section, Striking up a Relationship with Your Data. ) You can use this approach only when the two tables have a common key on which to join information. For example, if you refer to the ER diagram in Figure 2-1, you can see that a CustomerID key is common to both the SalesOrderHeader and Customer tables. Check out how this works on real tables. Say you want to query the account number, order date, and subtotal amount for SalesOrderHeaders (info that is stored in two separate tables the Customer table and the SalesOrderHeader table). To do that, you use the following syntax: SELECT s.AccountNumber, OrderDate, SubTotal FROM Sales.SalesOrderHeader s, Sales.Customer c WHERE s.customerID = c.customerID I referred to columns located in two tables SalesOrderHeader and Customer so that SQL Server needs to join the tables on a common key. The FROM clause contains the tables that contain the columns I want to seeas results. The WHERE clause indicates how the tables should be joined by their respective keys. This works here because customerID is the primary key for the Customer table, and it is also a foreign key for the SalesOrderHeader table. You ll notice that this query has what might be considered an interesting additional quirk. (That s how some refer to it, but others might use a different language.) First and foremost, the name customerID is clearly a column name that exists in both the SalesOrderHeader and Customer tables. (I say clearly because this whole process works only if tables share a common key and how could a key be common if it did not share the same name?) That sounds Chapter 2: Retrieving Data from a Database 37

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services