动态SQL查询小技巧

在SQL Server2005的SSRS教程中，使用报表中的动态查询引入了以下动态查询：

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &

"From HumanResources.EmployeeDepartmentHistory D " &

"INNER JOIN HumanResources.Employee E " &

"ON D.EmployeeID = E.EmployeeID " &

"INNER JOIN Person.Contact C " &

"ON E.ContactID = C.ContactID " &

Iif(Parameters!Department.Value = 0, "",  "WHERE D.DepartmentID = " & Parameters!Department.Value) &

"ORDER BY C.LastName"

其实可以简单地增加一个OR子句的查询，有效地禁止上述WHERE子句如果@参数为0：

WHERE D.DepartmentID = @Department OR @Department = 0

 

即是：

SELECT     c.FirstName, c.LastName, e.Title, d.DepartmentID

FROM         HumanResources.EmployeeDepartmentHistory AS d INNER JOIN

                      HumanResources.Employee AS e ON d.EmployeeID = e.EmployeeID INNER JOIN

                      Person.Contact AS c ON e.ContactID = c.ContactID

WHERE d.DepartmentID = @Department OR @Department = 0

ORDER BY c.LastName

这样会更安全，如果开发人员不小心暴露了输入参数，后一种语句更不容易受到SQL注入攻击。
在存储过程中需要返回的所有记录，这种方法也很适用。

 

P.S. 我在做SSRS开发时发现sql语句大小写敏感，可能是数据库配置问题。