Using Transact-SQL in SQL Server

SQL Server 2000 uses Transact-SQL (T-SQL), a vendor-specific version of the standard SQL. If you're familiar with Jet SQL, you'll find T-SQL similar. SQL Server uses T-SQL to process all its queries in the form of views, stored procedures, and even user-defined functions. In this section, you'll learn how to talk directly to SQL Server using T-SQL.

Tip 

For in-depth coverage of SQL, see Chapter 18

SQL Server Views

SQL Server uses a view to display the results of a SELECT statement. A view is simply a virtual table, which means the view doesn't actually contain data; a view shows a picture of the underlying tables, and the data stored there. Working with views is better than working directly with tables for the following reasons:

  • Views prevent users from modifying the underlying tables.

  • Views protect confidential data because you can limit the data the user sees.

  • Views free up resources and reduce network traffic by retrieving only the data the user needs at the requested time.

To create a view, use the following syntax:

CREATE VIEW [databasename.][owner.]viewname[(column [n,...])]  [WITH attribute [n,...]] AS  validselectstatement  [WITH CHECK OPTION]

Table 8.4 lists the attribute options for the WITH clause.

Table 8.4: WITH OPTIONS

Option

Description

ENCRYPTION

Encrypts the definition of the view. Be sure to keep the original scripts so you can modify the view if you choose. This option is useful only when you have a serious security requirement.

SCHEMABINDING

Prevents the user from making any changes to the underlying table.

VIEW_METADATA

Permits the use of client-side cursors with ODBC and OLEDB APIs.

To execute a view, simply call it in a SELECT statement in the following form:

SELECT fieldlist FROM dbo.viewname

Let's look at a simple example using the Northwind sample database that comes with SQL Server. Follow these steps:

  1. Open the Query Analyzer.

  2. Open a blank query window (if necessary) by choosing New from the New Query tool.

  3. Double-click Blank Query Window, or connect to the appropriate server by choosing File ®Connect.

  4. Specify Northwind by selecting it in the database drop-down list, which we've circled in Figure 8.1.

    click to expand
    Figure 8.1: Call a view in a SELECT statement to see its results.

  5. Create the following view, which displays each company and its orders:

    SELECT VIEW dbo.vw_CustomerOrders  AS SELECT dbo.Customers.CompanyName, dbo.Orders.OrderID FROM dbo.Customers INNER JOIN dbo.Orders ON   dbo.Customers.CustomerID = dbo.Orders.CustomerID

To view the results shown in Figure 8.1, open a new blank query window and refer to the view in the SELECT statement:

SELECT * FROM dbo.vw_CustomerOrders
Note 

SQL Server 2000 introduced indexed views because views, as simple as they sound, can actually slow things down because they're not optimized. All the computations take place when you execute the view. An indexed view has a unique clustered index. The query optimizer compiles an execution plan for the indexed view, so the evaluated view exists in physical storage. Consequently, the view runs much faster—almost immediately.

SQL Server Stored Procedures

A stored procedure is simply a set of precompiled T-SQL statements that are stored with the SQL Server database. Access users will find them similar to Access queries, but there's no graphical interface to help you write them—you must know T-SQL. Stored procedures provide many benefits, and you'll rely on them often for the following reasons:

  • They execute quickly because all checks and compiling have been performed.

  • They reduce network traffic by replacing Active Data Objects (ADOs) to retrieve data.

  • They improve security because users have access to the stored procedure, not the actual data on which the stored procedure is based.

Creating and Executing a Stored Procedure

Creating a stored procedure is simple, although the language used within a stored procedure can be extremely complex. To create a stored procedure use the CREATE PROCEDURE statement in the following form:

CREATE PROCEDURE procedurename  AS SQLstatement 

To execute a stored procedure use the EXECUTE or EXEC statement in one of the following forms:

EXECUTE procedurename  EXEC procedurename 

Passing Parameters

In Access, parameter queries pass user input to the query and thereby limit the results of the query at runtime. SQL Server's equivalent is the stored procedure that accepts parameters, which act as variables and simply pass values from one place to another. To create a parameter stored procedure use the following syntax:

CREATE PROCEDURE procedurename  (@parametername datatype)

Now, let's create a simple stored procedure that returns all the item records for a specific order, which you supply in the form of a parameter. Follow these steps:

  1. Open the SQL Server Query Analyzer.

  2. Select Northwind from the Database drop-down list.

  3. Enter the stored procedure shown in Listing 8.1 in the Query Analyzer, as shown in Figure 8.2.

    click to expand
    Figure 8.2: Enter the stored procedure in the Query Analyzer.

  4. Press F5 to save the stored procedure.

  5. To display all the items in order number 10428, enter the following statement in the Query Analyzer:

    EXEC sp_customer_parameter '10428'

    as shown in Figure 8.3. To display detail records about any order, simply execute the procedure and specify the orders corresponding to OrderID value.

    click to expand
    Figure 8.3: Call the stored procedure.

 On the CD-ROM   We strongly encourage you to key in all the code to create the example files yourself. To check your work, compare your files with the example files on the CD accompanying this book.

Listing 8.1: STORED PROCEDURE WITH PARAMETER

start example
CREATE PROCEDURE sp_customer_parameter  @OrderID int AS SELECT * FROM [Order Details]  WHERE [Order Details].OrderID = @OrderID
end example

Tip 

If the EXEC fails, make sure Northwind is selected in the Database drop-down list and try again.

Adding a Default to the Parameter

When providing a parameter stored procedure, the user might forget to provide the parameter, which will produce an error message. One way around this is to provide a default value, when applicable. Include a default parameter by specifying the default when you specify the parameter in the following form:

@parametername datatype = defaultvalue 

For instance, the parameter statement in sp_customer_parameter might resemble the following:

@OrderID int = '10428'

If you include the default value of 10428, you can execute the stored procedure without specifying a parameter value, and the call will return all the records for order 10428. Of course, you can still pass any valid value, other than the default, at any time. The default provides a shortcut when you use one value more than another, and it protects against errors if you forget to pass the parameter value.

SQL Server User-Defined Functions

User-defined functions (UDF) are new to SQL Server 2000, and it's about time! Other systems, such as Oracle and DB2, have provided them for a while. A UDF is a subroutine of encapsulated T-SQL code that you can call via T-SQL code, much like a native function. The major difference between a native function and a UDF is that you define the UDF's task and write the code necessary to get the job done. In addition, you can then modify the UDF at any later time.

A UDF can return a scalar value or a table variable (which is also new to SQL Server 2000). A table variable is similar to a temporary table in that it provides a set of temporary records, but it's available only within the scope of the calling function. The table variable is available to the user executing the function and while the function is in use. In addition, you can call a UDF from a SELECT statement. These are the major strengths of the SQL Server UDF.

The UDF may seem similar to the stored procedure, but the UDF provides different benefits:

  • You can execute a UDF from a SQL SELECT or SQL action query.

  • A UDF can return a table variable.

  • You can join to a UDF.

  • A UDF doesn't require much error-handling because T-SQL stops the function if an error occurs.

Creating a UDF

To create a UDF, use the CREATE FUNCTION statement in the following form:

CREATE FUNCTON ownerprefix.functionname  (@parameter AS datatype = defaultvalue ...)  RETURNS scalardatatype | RETURNS TABLE | RETURNS  @returnvariable TABLE  tabledefinition  WITH option AS BEGIN | RETURN                T-SQLstatement END

Like a stored procedure, you can pass parameters in the form of the optional @parameter argument. Table 8.5 lists and defines the CREATE FUNCTION arguments.

Table 8.5: CREATE FUNCTION ARGUMENTS

Argument

State

Description

ownerprefix

Required

Identifies the function's owner—dbo by default.

functionname

Required

Identifies the UDF by name. It must be unique within the database and to the owner.

@parameter

Optional

Allows the user to specify a passed value.

datatype

Required with parameter statement

Declares the parameter's datatype.

defaultvalue

Optional

Specifies a default value for the parameter.

RETURNS

Required

scalardatatype: Defines the UDF as a scalar function.

 

Required

TABLE: Defines the resulting table variable in the SELECT statement.

 

Required

@returnvariable TABLE tabledefinition: Specifies a table variable, identified by the @returnvariable argument, and tabledefinition defines the table datatype.

WITH

Optional

SCHEMABINDING: Binds the UDF to a table after which you can't make changes to the table.

 

Optional

ENCRYPTION: Encrypts the code and should be used with great care.

Note 

T-SQL requires the ownerprefix component to avoid ambiguous references. You might notice that many SQL Server developers include this prefix even when not required.

A Scalar UDF

A scalar UDF returns a single value. To create a scalar UDF, use the following syntax:

CREATE FUNCTION ownerprefix.functionname  (@parameter AS datatype = defaultvalue...)  RETURNS scalardatatype WITH option AS BEGIN     T-SQLstatement  END

For example, the UDF in Listing 8.2 accepts a parameter and returns a single value that represents the number of pages in a document, with 2200 being the approximate number of characters per page.

The user supplies the total number of characters in the document when running the UDF. Figure 8.4 shows the results of passing the value 35,000 to the UDF. As you can see, it returns 15—the estimated number of pages for a document with 35,000 characters.

Listing 8.2: PAGE COUNT UDF

start example
CREATE FUNCTION dbo.fnt_pagecount  (@characters int) RETURNS int AS BEGIN    RETURN(@characters/2200)  END
end example

click to expand
Figure 8.4: You can pass a parameter to a UDF.

Note 

When calling the UDF, you must enclose the arguments in parentheses as shown.

A Table-Returning Udf

A UDF can also return a table variable—a datatype that's new to SQL Server 2000. Table variables are similar to temporary tables, but more optimized, which makes them faster in most cases. You'll use a table-returning UDF to limit, modify, and insert new data. There are two types of table-returning UDFs:

Inline Consists of a simple SELECT statement

Multistatement Can contain many statements.

An Inline UDF

Because an inline UDF consists of a single SELECT statement, that statement defines the resulting table. These UDFs use the following syntax, which defines the table's columns and datatypes in the SELECT statement:

CREATE FUNCTION ownerprefix.functionname  (@parameter AS datatype = defaultvalue ...)  RETURNS TABLE WITH option AS RETURN selectstatement 

Listing 8.3 is an example of an inline UDF. It returns orders for a particular customer. Use the following SELECT statement to return orders for the customer represented by the text value VINET as shown in Figure 8.5:

click to expand
Figure 8.5: This UDF returns orders for a specific customer.

SELECT OrderID, ShippedDate  FROM dbo.fnt_shippedordersbycustomer('VINET')

Listing 8.3: UDF THAT RETURNS ORDERS

start example
CREATE FUNCTION dbo.fnt_shippedordersbycustomer  (@CustomerID nchar(5)) RETURNS TABLE AS RETURN (SELECT *  FROM dbo.Orders WHERE dbo.Orders.CustomerID = @CustomerID) 
end example

A Multistatement UDF

A UDF can require multiple SQL statements to return a table. When this is the case, you simply enclose each statement in a BEGIN/END block. The most significant difference between the inline and multistatement UDF is that the multistatement, also known as a multiline, defines the table's columns and datatypes in the TABLE statement. Use the following syntax to create a multistatement UDF:

CREATE FUNCTION ownerprefix.functionname  (@parameter AS datatype = defaultvalue ...)  RETURNS @returnvariable TABLE tabledefinition  WITH option AS BEGIN   T-SQL code   BEGIN     T-SQL code   END END

The UDF shown in Listing 8.4 defines the table variable and then inserts the results of a SELECT statement into the table. To create the UDF, enter it in the Query Analyzer window and then press F5. To return all the shipped dates for Nancy Davolio, as shown in Figure 8.6 run the following statement:

SELECT * FROM dbo.fnt_EmployeeOrders(1)

Listing 8.4: MULTISTATEMENT UDF

start example
CREATE FUNCTION dbo.fnt_employeeorders  (@EmployeeID int)  RETURNS @EmployeeOrders TABLE ( FirstName nvarchar(10), LastName nvarchar(20), OrderID int, Shipped DateTime ) AS BEGIN   INSERT @EmployeeOrders   SELECT dbo.Employees.FirstName,      dbo.Employees.LastName, dbo.Orders.OrderID,      dbo.Orders.ShippedDate   FROM dbo.Orders   INNER JOIN dbo.Employees ON      dbo.Orders.EmployeeID =      dbo.Employees.EmployeeID      WHERE dbo.Employees.EmployeeID =            @EmployeeID   RETURN  END
end example

click to expand
Figure 8.6: Return all the orders for a specific employee.

SQL Aggregate Functions

Unlike Access 2002, SQL Server doesn't provide domain functions. But you can use T-SQL aggregate functions to process sets of records and even limit those sets by using criteria in the form of a SQL WHERE clause. T-SQL aggregate functions aren't as limited as Jet SQL or Access 2002's domain functions because the WHERE clause supplies a means of filtering the data.

Avg(ALL|DISTINCT domain)

The AVG() function returns the average value of a set of values. For instance, the following statement returns the value 26.2185—the average unit price in the Order Details table in the Northwind sample database that comes with SQL Server.

USE Northwind SELECT Avg([Order Details].UnitPrice) AS AvgPrice  FROM [Order Details]

The following statement limits the set of records considered in the calculation:

USE Northwind SELECT Avg([Order Details].UnitPrice) AS AvgPrice FROM [Order Details]  WHERE [Order Details].OrderID = '10248'

The average unit price for the order 10248 is 19.5333.

If you omit the ALL or DISTINCT predicate, T-SQL defaults to ALL. In this case, all the records are included in the calculation. The DISTINCT keyword limits the calculation to unique values.

Count(ALL|DISTINCT domain)

The Count() function returns the number of non-Null values in a set of values. The following statement returns the number of orders in the Orders table, which happens to be 830.

USE Northwind SELECT Count(Orders.OrderID)  FROM Orders

The WHERE clause in the following statement limits the count to a particular customer. The following statement returns the value 5.

USE Northwind SELECT Count(Orders.OrderID)  FROM Orders WHERE CustomerID = 'VINET'

If you omit the ALL or DISTINCT predicate, T-SQL defaults to ALL. In this case, all the records are included in the calculation. The DISTINCT keyword limits the calculation to unique values.

Count(*)

This form of the Count() function counts the total number of rows. The result of a similar Count(domain) and Count(*) function can be the same. For instance, a quick count of the rows in the Employees table tells us there are nine employee records:

USE Northwind SELECT Count(*)  FROM Employees

If you specify a count of the Region field, you find that only five employees have a geographical region listed:

USE Northwind SELECT Count(Employees.Region)  FROM Employees

Sum(ALL|DISTINCT domain)

The Sum() function adds all the values in a set of values. For instance, the following statement returns the total unit price sum of all the orders, 56500.91, although business-wise, it doesn't make much sense to do so.

USE Northwind SELECT Sum([Order Details].UnitPrice)  FROM [Order Details]

If you omit the ALL or DISTINCT predicate, T-SQL defaults to ALL. In this case, all the records are included in the calculation. The DISTINCT keyword limits the calculation to unique values.

Min(domain)

The Min() function returns the smallest value in a set of records. The following statement returns the minimum unit price, 2.50.

USE NorthwindSELECT Min(Products.UnitPrice) FROM Products

The following statement limits the search to only those products that are in a particular category— the resulting value is 4.50.

USE Northwind SELECT Min(Products.UnitPrice)  FROM Products  WHERE CategoryID = '1'

Max(domain)

The Max() function is similar to the Min() function, except it returns the largest value in a set of records. The following statement returns the maximum unit price value, which is 263.50.

USE Northwind SELECT Max(Products.UnitPrice)  FROM Products

To find the highest-priced product in a particular category, use the following statement, which returns the value 43.90.

USE Northwind SELECT Max(Products.UnitPrice)  FROM Products  WHERE CategoryID = '2'

StDev(domain)

The StDev() function estimates the standard deviation for a set of records. The following statement returns the standard deviation for freight, which is 44.41, in the region represented by the value 'RJ'.

USE Northwind SELECT StDev(Orders.Freight)  FROM Orders WHERE ShipRegion = 'RJ' 

StDevP(domain)

The StDevP() function evaluates the standard deviation for a population for a set of records. The following statement returns the standard deviation for population for freight, which is 43.75 in the region represented by the value 'RJ'.

USE Northwind SELECT StDevP(Orders.Freight)  FROM Orders WHERE ShipRegion = 'RJ'

Var(domain)

The Var() function estimates variance. The following statement estimates the variance for freight, which is 1971.85 on orders shipped to the region represented by the value 'RJ'.

USE Northwind SELECT Var(Orders.Freight)  FROM Orders WHERE ShipRegion = 'RJ'

VarP(domain)

The VarP() function evaluates the variance by population. The following statement evaluates the variance across the population for the freight, which is 1913.86 shipped to the region represented by the value 'RJ'.

USE Northwind SELECT VarP(Orders.Freight)  FROM Orders WHERE ShipRegion = 'RJ'



Mastering Dreamweaver MX Databases
Mastering Dreamweaver MX Databases
ISBN: 078214148X
EAN: 2147483647
Year: 2002
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net