核心在于拼接SQL字符串中遇到中文双引号问题：
可以使用系统函数 替换掉set @pageStr = replace(@queryStr,'"','''') 不过更推荐 使用两个单引号转译
USE [TEMP]
GO /****** Object: StoredProcedure [dbo].[P_GetAlterAsset]
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO ALTER proc [dbo].[P_GetAlterAssetPage]
(
@filterCondition varchar(max)='',
@pageIndex int,
@pageSize int,
@totalCount int output
)
AS Begin
DECLARE @queryStr NVARCHAR(MAX)=''
DECLARE @pageStr NVARCHAR(MAX)=''
DECLARE @endStr NVARCHAR(MAX)=''
DECLARE @countStr NVARCHAR(MAX)=''
DECLARE @and NVARCHAR(MAX)='' set @and = @filterCondition
set @queryStr = '
WITH T1
AS ( SELECT ALDE_MSNo
FROM T_Alter_Apply
LEFT JOIN T_Alter_Detail ON ALDE_ApplyID = AP_ID
WHERE AP_StateID = 1
UNION
SELECT [GBDE_MSNo]
FROM [dbo].[T_Giveback_Apply]
LEFT JOIN [dbo].[T_GiveBack_Detail] ON [AP_ID] = [GBDE_APID]
WHERE AP_State = 1
),
T4
AS ( SELECT al.AL_State ,
ali.ALI_ASNo ,
al.[AL_ID]
FROM [dbo].[T_Alter] al
inner JOIN [dbo].[T_AlterItem] ali ON al.[AL_AlterNo] = ali.[ALI_AlterNo]
AND al.AL_State <> 100 ), T2
AS ( SELECT DISTINCT
b.AS_TypeName ,
b.AS_MSNo ,
b.AS_BrandName ,
b.AS_Model ,
c.AD_CPU ,
c.AD_HardDisk ,
c.AD_Memory ,
c.AD_VideoCard AS AD_SoundCard ,
b.AS_Price ,
b.AS_State ,
b.AS_CostCenterName ,
b.AS_CostCenterNo ,
b.AS_Category,
b.AS_PlaceNo,
b.AS_PlaceName,
b.AS_ProjectNo,
b.AS_ProjectName,
b.AS_VestInNo,
b.AS_VesInName,
b.AS_IsSpecialPro,
b.AS_ProfitCenterNo ,
b.AS_ProfitCenterName,
b.AS_ComNo,
b.AS_ComName,
CASE WHEN T1.ALDE_MSNo IS NOT NULL
THEN "变更中"
WHEN ISNULL(T4.AL_State, 2) = 2
THEN "可变更"
ELSE "变更中"
END AS AL_StateName
FROM dbo.T_Asset b with(nolock)
LEFT JOIN dbo.T_AssetDetail c with(nolock) ON b.AS_ADID = c.AD_ID
LEFT JOIN T4 with(nolock) ON b.AS_MSNo = T4.ALI_ASNo
LEFT JOIN T1 with(nolock) ON b.AS_MSNo = T1.ALDE_MSNo
LEFT JOIN dbo.V_PSA_PrjInfo p with(nolock) ON b.AS_ProjectNo=p.PrjCode
WHERE b.AS_State = 1
and (p.PrjTypeID NOT IN ( 710, 711 ) or p.PrjTypeID is null)
and (p.PrjStatus IN ( 6, 10 ) or p.PrjStatus is null )
'+@and + ')' set @pageStr = replace(@queryStr,'"','''')
set @endStr = @pageStr+' SELECT * FROM (select row_number() over(order by AS_Price ) as rowIndex,* FROM T2 ) tt where tt.rowIndex between '+ CAST(((@pageIndex-1)*@pageSize + 1) as nvarchar(20)) +' and '+ CAST((@pageIndex*@pageSize) as nvarchar(20)) DECLARE @strCountSql NVARCHAR(max)
SET @strCountSql = @queryStr +' Select @RecordCount = count(*) FROM T2 '
EXEC sp_executesql @strCountSql, N'@RecordCount int OUTPUT', @totalCount OUTPUT execute (@endStr) END GO
此文仅作用于学习记录，之前写的为一个视图，在功能中又在外面嵌套各种条件进行查询，相当于查询所有数据后再进行分页，速度很慢，特别耗费服务器cpu资源；
把一个视图修改为两个存储过程，一个分页，一个查询所有；调用代码如下：
#region 优化为存储过程 20190104
var parameters = new List<System.Data.SqlClient.SqlParameter>();
parameters.Add(new System.Data.SqlClient.SqlParameter("@filterCondition", sql));
parameters.Add(new System.Data.SqlClient.SqlParameter("@pageIndex", pageIndex));
parameters.Add(new System.Data.SqlClient.SqlParameter("@pageSize", pageSize));
SqlParameter outParameter = new SqlParameter("@totalCount", SqlDbType.Int, );
outParameter.Direction = ParameterDirection.Output;
parameters.Add(outParameter);
var lstAssets = db.Database.SqlQuery<AssetAlterInfo>("exec P_EAM_GetAlterAssetPage @filterCondition, @pageIndex, @pageSize, @totalCount out", parameters.ToArray()).ToList();
count = Convert.ToInt32(outParameter.Value == DBNull.Value ? : outParameter.Value);
存储过程分页