- PR -

カスタム・ページング/ソーティングのSQL

1
投稿者投稿内容
Furi2
ベテラン
会議室デビュー日: 2004/10/28
投稿数: 74
お住まい・勤務地: N.Hollywood/Agoura Hills
投稿日時: 2007-02-05 15:51
こんにちは、お世話になっています。

現在ASP.NETのGridViewを使ったカスタム・ページング/ソーティングを計画中なのですが、それに見合ったストアド・プロシージャをどうしたものか悩んでいます。(SQL Server 2005を使用)

下記のページより、動的SQLがよい解決策であるように書かれていたので考えてみたのですが、

http://japan.internet.com/developer/20060509/26.html

-- 上記サイトからの引用
DECLARE @sql nvarchar(4000)
SET @sql = ’SELECT EmployeeID, LastName, FirstName, DepartmentID,
Salary, HireDate, DepartmentName
FROM
(SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,
HireDate, d.Name as DepartmentName,
ROW_NUMBER() OVER(ORDER BY ’ + @sortExpression + ’)
as RowNum
FROM Employees e
INNER JOIN Departments d ON
e.DepartmentID = d.DepartmentID
) as EmpInfo
WHERE RowNum BETWEEN ’ + CONVERT(nvarchar(10), @startRowIndex) +
’ AND (’ + CONVERT(nvarchar(10), @startRowIndex) + ’ + ’
+ CONVERT(nvarchar(10), @maximumRows) + ’) - 1’

-- Execute the SQL query
EXEC sp_executesql @sql


私の場合、ベースとなるクエリーに条件がつくので、

DECLARE @sql nvarchar(4000)
SET @sql = ’SELECT EmployeeID, LastName, FirstName, DepartmentID,
Salary, HireDate, DepartmentName
FROM
(SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,
HireDate, d.Name as DepartmentName,
ROW_NUMBER() OVER(ORDER BY ’ + @sortExpression + ’)
as RowNum
FROM Employees e
INNER JOIN Departments d ON
e.DepartmentID = d.DepartmentID
  WHERE LastName = \'' + @LastName + '\' --- この部分に値が動的に変わる条件が必要。
) as EmpInfo
WHERE RowNum BETWEEN ’ + CONVERT(nvarchar(10), @startRowIndex) +
’ AND (’ + CONVERT(nvarchar(10), @startRowIndex) + ’ + ’
+ CONVERT(nvarchar(10), @maximumRows) + ’) - 1’
ESCAPE '\'

-- Execute the SQL query
EXEC sp_executesql @sql


となります。ですがこの形ですと、結果的に文字列をそのまま条件として受け渡している形になってしまい、パラメータ使用による恩恵が失われてしまっているので、何とか避けたいのです。

何かよいお知恵を拝借できれば、大変感謝します。
Furi2
ベテラン
会議室デビュー日: 2004/10/28
投稿数: 74
お住まい・勤務地: N.Hollywood/Agoura Hills
投稿日時: 2007-02-05 15:55
エスケープに使ったバックスラッシュがうまく表示されていないようです。SQLの細かいシンタックスは無視してください、、すいません。
がんふぃーるど
ベテラン
会議室デビュー日: 2006/06/05
投稿数: 58
お住まい・勤務地: さいたま
投稿日時: 2007-02-05 17:38
がんふぃーるどです。お世話様です。

私の考える得る限りでは、解決策は二つあります。

一つは、SQLクエリの構築をストアドプロシージャではなく、.NET側のコンポーネントで実装する方法。

もう一つは、ストアドプロシージャ内にて自力でパラメタライズドクエリを書く方法。パラメタライズドクエリの記述方法はT-SQLのヘルプのsp_executesqlを見ると良いです。
一応修正方法も少し書いておきます。(@LastNameはvarchar(20)と勝手に定義してます。自分の環境に合わせて修正して下さい)
WHERE LastName = '' + @LastName + ''
-> WHERE LastName = @LastName
EXEC sp_executesql @sql
-> EXEC sp_executesql @sql,N'@LastName varchar(20)', @LastName

# シングルクォテーションが一つ抜けていたので修正

[ メッセージ編集済み 編集者: がんふぃーるど 編集日時 2007-02-05 17:48 ]
Furi2
ベテラン
会議室デビュー日: 2004/10/28
投稿数: 74
お住まい・勤務地: N.Hollywood/Agoura Hills
投稿日時: 2007-02-05 18:53
引用:

-> EXEC sp_executesql @sql,N'@LastName varchar(20)', @LastName



なるほど!!目からウロコです。。仰るとおりsp_excutesqlのヘルプが参考になりそうです。

また、条件もごちゃごちゃするので、.NET側で何とかするのも、悪くないような気がしてきました。

がんふぃーるどさん、本当にありがとうございました!!



実は、わらにもすがる思いで他のサイトにも同じ質問をしたのですが(日本のサイトではありません)、レスはたくさん頂けるのですが何だか検討違いのコメントだらけで、改めてここの会議室は質が高いなぁ、と感動しました。これからもよろしくお願いいたします。
1

スキルアップ/キャリアアップ(JOB@IT)