在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语句大小写敏感,可能是数据库配置问题。