| 在实际的项目中，我们经常会碰到存储过程中需要使用sp_executesql语句的情形，如下：
1
IF EXISTS (
SELECT *
FROM sysobjects
WHERE type =
'P'
AND name =
'GetEmployeesByFilter')
2
BEGIN
3
DROP Procedure [GetEmployeesByFilter]
4
END
5
6 GO
7
8
CREATE Procedure [GetEmployeesByFilter]
9 (
10 @EmployeeNo
NVarChar(50) =
NULL,
11 @EmployeeName
NVarChar(50) =
NULL,
12 @DepartmentId
Int =
NULL,
13 @PositionId
Int =
NULL,
14 @EmployeeManager
Int =
NULL,
15 @BeginEmployeeEntryDate
DateTime =
NULL,
16 @EndEmployeeEntryDate
DateTime =
NULL,
17 @EmployeeStatus
Int =
NULL,
18 @PageSize
Int =
NULL,
19 @PageIndex
Int =
NULL,
20 @RecordCount
Int =
NULL OUTPUT
21 )
22
23
AS
24
25
BEGIN
26
DECLARE @MinIndex
Int
27
DECLARE @MaxIndex
Int
28
SET @MinIndex = (@PageIndex - 1) * @PageSize + 1
29
SET @MaxIndex = @MinIndex + @PageSize - 1
30
31
DECLARE @Where
NVarChar(
MAX)
32
SET @Where =
'0 = 0'
33
IF @EmployeeNo
IS NOT NULL
34
SET @Where = @Where +
' AND [EmployeeNo] LIKE ''%' + @EmployeeNo +
'%'''
35
IF @EmployeeName
IS NOT NULL
36
SET @Where = @Where +
' AND [EmployeeName] LIKE ''%' + @EmployeeName +
'%'''
37
IF @DepartmentId
IS NOT NULL
38
SET @Where = @Where +
' AND [DepartmentId] = ''' +
CONVERT(
NVarChar, @DepartmentId) +
''''
39
IF @PositionId
IS NOT NULL
40
SET @Where = @Where +
' AND [PositionId] = ''' +
CONVERT(
NVarChar, @PositionId) +
''''
41
IF @EmployeeManager
IS NOT NULL
42
SET @Where = @Where +
' AND [EmployeeManager] = ''' +
CONVERT(
NVarChar, @EmployeeManager) +
''''
43
IF @BeginEmployeeEntryDate
IS NOT NULL
44
SET @Where = @Where +
' AND [EmployeeEntryDate] >= ''' +
CONVERT(
NVarChar, @BeginEmployeeEntryDate, 101) +
' ' +
'00:00:00' +
''''
45
IF @EndEmployeeEntryDate
IS NOT NULL
46
SET @Where = @Where +
' AND [EmployeeEntryDate] <= ''' +
CONVERT(
NVarChar, @EndEmployeeEntryDate, 101) +
' ' +
'23:59:59' +
''''
47
IF @EmployeeStatus
IS NOT NULL
48
SET @Where = @Where +
' AND [EmployeeStatus] = ''' +
CONVERT(
NVarChar, @EmployeeStatus) +
''''
49
50
DECLARE @Record
NVarChar(
MAX)
51
SET @Record =
'SELECT ROW_NUMBER() OVER(ORDER BY [EmployeeId]) AS [Index],
52
[EmployeeId],
53
[EmployeeNo],
54
[EmployeeName],
55
[DepartmentId],
56
[PositionId],
57
[EmployeeManager],
58
[EmployeeGender],
59
[EmployeeEntryDate],
60
[EmoplyeeBirthday],
61
[EmployeePhone],
62
[EmployeeEmail],
63
[EmployeeStatus]
64
FROM [Employee]
65
WHERE' +
' ' + @Where
66
67
DECLARE @Sql
NVarChar(
MAX)
68
SET @Sql =
'SELECT @RecordCount = COUNT(*)
69
FROM (' + @Record +
') DERIVEDTBL
70
71
SELECT [EmployeeId],
72
[EmployeeNo],
73
[EmployeeName],
74
[DepartmentId],
75
[PositionId],
76
[EmployeeManager],
77
[EmployeeGender],
78
[EmployeeEntryDate],
79
[EmoplyeeBirthday],
80
[EmployeePhone],
81
[EmployeeEmail],
82
[EmployeeStatus]
83
FROM (' + @Record +
') DERIVEDTBL
84
WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'
85
86
DECLARE @Parameter
NVarChar(
MAX)
87
SET @Parameter =
'@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'
88
89
EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount
OUTPUT
90
END
91
92 GO
1
<
Function
Name
="
dbo.GetEmployeesByFilter"
Method
="
GetEmployeesByFilter"
>
2
<
Parameter
Name
="
EmployeeNo"
Parameter
="
employeeNo"
Type
="
System.String"
DbType
="
NVarChar(50)"
/>
3
<
Parameter
Name
="
EmployeeName"
Parameter
="
employeeName"
Type
="
System.String"
DbType
="
NVarChar(50)"
/>
4
<
Parameter
Name
="
DepartmentId"
Parameter
="
departmentId"
Type
="
System.Int32"
DbType
="
Int"
/>
5
<
Parameter
Name
="
PositionId"
Parameter
="
positionId"
Type
="
System.Int32"
DbType
="
Int"
/>
6
<
Parameter
Name
="
EmployeeManager"
Parameter
="
employeeManager"
Type
="
System.Int32"
DbType
="
Int"
/>
7
<
Parameter
Name
="
BeginEmployeeEntryDate"
Parameter
="
beginEmployeeEntryDate"
Type
="
System.DateTime"
DbType
="
DateTime"
/>
8
<
Parameter
Name
="
EndEmployeeEntryDate"
Parameter
="
endEmployeeEntryDate"
Type
="
System.DateTime"
DbType
="
DateTime"
/>
9
<
Parameter
Name
="
EmployeeStatus"
Parameter
="
employeeStatus"
Type
="
System.Int32"
DbType
="
Int"
/>
10
<
Parameter
Name
="
PageSize"
Parameter
="
pageSize"
Type
="
System.Int32"
DbType
="
Int"
/>
11
<
Parameter
Name
="
PageIndex"
Parameter
="
pageIndex"
Type
="
System.Int32"
DbType
="
Int"
/>
12
<
Parameter
Name
="
RecordCount"
Parameter
="
recordCount"
Type
="
System.Int32"
DbType
="
Int"
Direction
="
InOut"
/>
13
<
Return
Type
="
System.Int32"
DbType
="
Int"
/>
14
</
Function
>
遇到这种情况，我们该怎么处理呢？这里提供两种解决方案： 1、直接更改DBML文件，将返回值改成一个空的结果集。
1
<
Function
Name
="
dbo.GetEmployeesByFilter"
Method
="
GetEmployeesByFilter"
>
2
<
Parameter
Name
="
EmployeeNo"
Parameter
="
employeeNo"
Type
="
System.String"
DbType
="
NVarChar(50)"
/>
3
<
Parameter
Name
="
EmployeeName"
Parameter
="
employeeName"
Type
="
System.String"
DbType
="
NVarChar(50)"
/>
4
<
Parameter
Name
="
DepartmentId"
Parameter
="
departmentId"
Type
="
System.Int32"
DbType
="
Int"
/>
5
<
Parameter
Name
="
PositionId"
Parameter
="
positionId"
Type
="
System.Int32"
DbType
="
Int"
/>
6
<
Parameter
Name
="
EmployeeManager"
Parameter
="
employeeManager"
Type
="
System.Int32"
DbType
="
Int"
/>
7
<
Parameter
Name
="
BeginEmployeeEntryDate"
Parameter
="
beginEmployeeEntryDate"
Type
="
System.DateTime"
DbType
="
DateTime"
/>
8
<
Parameter
Name
="
EndEmployeeEntryDate"
Parameter
="
endEmployeeEntryDate"
Type
="
System.DateTime"
DbType
="
DateTime"
/>
9
<
Parameter
Name
="
EmployeeStatus"
Parameter
="
employeeStatus"
Type
="
System.Int32
>"
DbType
="
Int"
/>
10
<
Parameter
Name
="
PageSize"
Parameter
="
pageSize"
Type
="
System.Int32"
DbType
="
Int"
/>
11
<
Parameter
Name
="
PageIndex"
Parameter
="
pageIndex"
Type
="
System.Int32"
DbType
="
Int"
/>
12
<
Parameter
Name
="
RecordCount"
Parameter
="
recordCount"
Type
="
System.Int32"
DbType
="
Int"
Direction
="
InOut"
/>
13
<
ElementType
Name
="
GetEmployeesByFilterResult"
/>
14
</
Function
>
2、保持存储过程GetEmployeesByFilter不变，再创建另外一个存储过程GetEmployeesByFilterCalling，然后在存储过程GetEmployeesByFilterCalling中调用存储过程GetEmployeesByFilter（EXEC GetEmployeesByFilter）。 （感谢Microsoft公司的Brandon Wang提供的这个方案。不过对不住的是，在我的系统中第一种方案用起来似乎更为方便些。） |
本文转自 Eallies 51CTO博客，原文链接：http://blog.51cto.com/eallies/79030，如需转载请自行联系原作者
