ヒント2： セッション変数およびストアドプロシージャパラメータの定義
表1および表2は、検索ページのセッション変数とストアドプロシージャパラメータをそれぞれ示しています。
| 変数 | 説明 |
| CurrentFirstRow | ページングされた結果セットの先頭の行番号 |
| CurrentLastRow | ページングされた結果セットの末尾の行番号 |
| SortCol | 現在のソート列 |
| StartRowIndex | ページングする場合に取得する先頭行 |
| 変数 | 説明 |
| FirstName | FirstNameの検索テキスト |
| LastName | LastNameの検索テキスト |
| Address | Addressの検索テキスト |
| City | Cityの検索テキスト |
| State | Stateの検索テキスト |
| Zip | Zipの検索テキスト |
| StartRowIndex | 取得する先頭行 |
| MaxRows | 取得する最大行数 |
| AlphaChar | ナビゲート先の英字 |
| SortCol | ソートの基準となる列 |
結果セットページには決まった行数しか表示されないため、現在の先頭行インデックスと末尾行インデックスをロジックで保持して、前後のページへのナビゲーションに対処する必要があります。ここで、簡単なシナリオを考えてみましょう。ユーザーが最初に15行の結果セットをもたらすような条件を「City」列で選択したとします。このとき、検索ページに一度に表示できるのは4行だけとします。
1 ANDERSON 2 ARTHUR 3 BARTON 4 BOUTON 5 DEIDRICK 6 DOBSON 7 HAMILTON 8 JERICHO 9 MONTGOMERY 10 RIDDLEY 11 STEVENS 12 TILLY 13 WILCOX 14 WILLIAMS 15 ZEUSS
最初の実行時、ストアドプロシージャパラメータのStartRowIndexおよびAlphaCharはそれぞれ0と空白であり、ストアドプロシージャは最初の4行を返します。ページの分離コードでは、CurrentFirstRowおよびCurrentLastRowの値を1と4にそれぞれ設定します。
ユーザーが次のページにナビゲートする場合、分離コードのロジックでは、ストアドプロシージャのStartRowIndexパラメータを5（CurrentLastRow + 1）に設定したうえで、ストアドプロシージャを再び呼び出します。ストアドプロシージャは、「DEIDRICK」～「JERICHO」の行を返し、Webページの分離コードはCurrentFirstRowおよびCurrentLastRowをそれぞれ5と8に設定します。1つ前のページに戻る場合は、分離コードのロジックにより、StartRowインデックスが、セッション変数CurrentFirstRowの値からセッション変数MaxRowsの値を引いた値に設定されます。
TopボタンとBottomボタンについては、分離コードによって、StartRowIndexを0または-1（ストアドプロシージャは-1を処理します）に設定します。
最後に、特定の文字（例えば「R」）に直接ジャンプする場合は、ストアドプロシージャのAlphaCharがRに設定され、「RIDDLEY」～「WILCOX」の行が返されます。分離コードにより、セッション変数のCurrentFirstRowおよびCurrentLastRowは、それぞれ10と13に設定されます。
変数の処理については、いくつか注意すべき点があります。
- ユーザーが特定の文字（または数字）にジャンプする場合、
StartRowIndexパラメータは使用されません。 - 逆に、ユーザーが通常のナビゲーションボタン（Top、Next、Previous、End）を使用する場合、
AlphaCharパラメータは使用されません。
ヒント3： 【重要ポイント】T-SQL 2005の新しいランキング関数
リスト1は、結果セットを返す完全なストアドプロシージャです（話の結末を先に知りたいタイプの人は、まずリスト1を見てください）。次のコードは、ヒント2のSQLパラメータを使って、SQL Management Studio（図5を参照）で対話的にストアドプロシージャをテストする場合のサンプルのSQLファイルを示しています。
DECLARE @LastName varchar(50), @FirstName varchar(50), @Address varchar(50), @City varchar(50), @State varchar(2), @Zip varchar(50), @StartRowIndex int, @MaxRows int, @Alphachar varchar(1) , @SortCol varchar(20) SET @STATE = 'NY' SET @MaxRows = 100 SET @startRowIndex = 0 SET @SortCol = 'ADDRESS' SET @AlphaChar = '' EXEC [dbo].[LookupEmployees] @LastName, @FirstName , @Address , @city, @state, @zip, @startRowIndex , @MaxRows , @alphachar, @SortCol
この記事の以降の3つのヒントでは、ストアドプロシージャの各部を個別に説明します。
ヒント2のサンプルデータでは、検索条件（名前や住所など）に一致するレコードごとに、シーケンシャルな行番号が対応していました。今回のストアドプロシージャでは、T-SQL 2005の新しいROW_NUMBER関数を使って、条件に基づいてこの行番号を割り当てます。ROW_NUMBERを使うことで開発者はシーケンシャルなランキング番号を割り当てることができ、ORDER BY文でコーディングする場合と同じようにランキングを並べることができます。
SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, ROW_NUMBER() OVER (ORDER BY CASE @SortCol WHEN 'LASTNAME' THEN LastName+Firstname WHEN 'ADDRESS' THEN Address WHEN 'CITY' THEN City+LastName+Firstname WHEN 'STATE' THEN State+LastName+Firstname WHEN 'ZIP' THEN Zip+LastName+Firstname ELSE LastName + Firstname END) AS RowNum FROM Customers
次のヒントでは、特定のページ/行インデックスの範囲の結果だけを返す、ランキングのフィルタリングについて説明します。
ヒント4： オプションの検索パラメータおよびページ/行インデックス範囲の処理
ストアドプロシージャには検索条件用のさまざまなパラメータが含まれていますが、一度に使用するパラメータの数は限られています。例えば、エンドユーザーが検索する情報として考えられるのは名前、住所、または電話番号のみです。ストアドプロシージャは、ユーザーが指定した入力条件についてのみクエリを実行します。開発者によっては、動的SQL文を作成することによってこれを処理します。T-SQLのCOALESCE関数を使用する方法もあります。この関数の場合、NULL入力パラメータのチェックが行われます。
また、このストアドプロシージャでは、LIKE文を使って部分テキスト検索を実装します。
WHERE LastName LIKE '%' + COALESCE(@LastName,LastName)+ '%' AND Address LIKE '%' + COALESCE(@Address,Address) + '%' AND ...
さらに、このストアドプロシージャでは、数値のページングの場合は行の範囲でフィルタを行い、クイックナビゲーションの場合は特定の文字または値で始まる現在のソート列に基づいてフィルタを行う必要があります。ここまでくるとストアドプロシージャがやや複雑になってきます。この処理を実現するには、WHERE句にインラインのCASE文を記述します。
WHERE CASE WHEN @lPaging = 1 AND @SortCol= 'LASTNAME' AND SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar) THEN 1 WHEN @lPaging = 1 AND @SortCol= 'ADDRESS' AND SUBSTRING(Address,1,1) >= RTRIM(@AlphaChar) THEN 1 WHEN @lPaging = 0 AND RowNum BETWEEN ( CASE @StartRowIndex WHEN -1 THEN (RecCount ) - @MaxRows ELSE @StartRowIndex END ) AND (CASE @StartRowIndex WHEN -1 THEN ( RecCount ) - @MaxRows ELSE @StartRowIndex END ) + @maxRows THEN 1
ヒント5： クエリの連携
ヒント3では、ランキング番号を生成する関数を取り上げました。そして、ヒント4では、2種類のT-SQL WHERE句を取り上げました。1つは最初のクエリに含まれますが、もう1つは、行インデックス範囲または1文字の英数字に基づいて元の結果セットをさらにフィルタするものです。最初の結果セットをさらにフィルタするという概念は、次のヒントへと続きます。
SQL 2005より前のバージョンでは、中間結果に対してさらにSQL文を記述する場合、開発者は派生テーブル、テーブル変数、一時テーブルなどを使用していました。SQL 2005では、共通テーブル式（CTE）が導入されました。CTEは、基本的には、後に続く1つのステートメントで参照できる動的なビューです。
CTEは、WITH文で簡単に作成できます。次の例では、CustListTempという名前のCTEを作成しています。
WITH CustListTemp AS (SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, ROW_NUMBER() OVER (ORDER BY….. )
このCTEをクエリするコードを作成することができます。CTEを参照できるのは、次に続くSQL文だけです。CTEに対するクエリは、実際にはサブクエリを実行して、行のCOUNTを決定していることに注意してください。ユーザーが最後のMAXROW行数を表示する場合、クエリ内の後のWHERE句では、CTEから条件付きでCOUNTを調べます。
SELECT TOP (@MaxRows) CustomerID, LastName, FirstName, Address, City, State, Zip, RowNum FROM (SELECT CustListTemp.*, (SELECT COUNT(*) from CustListTemp) AS RecCount FROM CustListTemp) CustList WHERE...
