核心在于拼接SQL字符串中遇到中文双引号问题：
可以使用系统函数 替换掉set @pageStr = replace(@queryStr,'"','''') 不过更推荐 使用两个单引号转译
1 USE [TEMP] 2 GO 3 4 /****** Object: StoredProcedure [dbo].[P_GetAlterAsset] 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 12 13 14 15 16 17 ALTER proc [dbo].[P_GetAlterAssetPage] 18 ( 19 @filterCondition varchar(max)='', 20 @pageIndex int, 21 @pageSize int, 22 @totalCount int output 23 ) 24 AS Begin 25 DECLARE @queryStr NVARCHAR(MAX)='' 26 DECLARE @pageStr NVARCHAR(MAX)='' 27 DECLARE @endStr NVARCHAR(MAX)='' 28 DECLARE @countStr NVARCHAR(MAX)='' 29 DECLARE @and NVARCHAR(MAX)='' 30 31 32 33 set @and = @filterCondition 34 set @queryStr = ' 35 WITH T1 36 AS ( SELECT ALDE_MSNo 37 FROM T_Alter_Apply 38 LEFT JOIN T_Alter_Detail ON ALDE_ApplyID = AP_ID 39 WHERE AP_StateID = 1 40 UNION 41 SELECT [GBDE_MSNo] 42 FROM [dbo].[T_Giveback_Apply] 43 LEFT JOIN [dbo].[T_GiveBack_Detail] ON [AP_ID] = [GBDE_APID] 44 WHERE AP_State = 1 45 ), 46 T4 47 AS ( SELECT al.AL_State , 48 ali.ALI_ASNo , 49 al.[AL_ID] 50 FROM [dbo].[T_Alter] al 51 inner JOIN [dbo].[T_AlterItem] ali ON al.[AL_AlterNo] = ali.[ALI_AlterNo] 52 AND al.AL_State <> 100 53 54 ), 55 56 T2 57 AS ( SELECT DISTINCT 58 b.AS_TypeName , 59 b.AS_MSNo , 60 b.AS_BrandName , 61 b.AS_Model , 62 c.AD_CPU , 63 c.AD_HardDisk , 64 c.AD_Memory , 65 c.AD_VideoCard AS AD_SoundCard , 66 b.AS_Price , 67 b.AS_State , 68 b.AS_CostCenterName , 69 b.AS_CostCenterNo , 70 b.AS_Category, 71 b.AS_PlaceNo, 72 b.AS_PlaceName, 73 b.AS_ProjectNo, 74 b.AS_ProjectName, 75 b.AS_VestInNo, 76 b.AS_VesInName, 77 b.AS_IsSpecialPro, 78 b.AS_ProfitCenterNo , 79 b.AS_ProfitCenterName, 80 b.AS_ComNo, 81 b.AS_ComName, 82 CASE WHEN T1.ALDE_MSNo IS NOT NULL 83 THEN "变更中" 84 WHEN ISNULL(T4.AL_State, 2) = 2 85 THEN "可变更" 86 ELSE "变更中" 87 END AS AL_StateName 88 FROM dbo.T_Asset b with(nolock) 89 LEFT JOIN dbo.T_AssetDetail c with(nolock) ON b.AS_ADID = c.AD_ID 90 LEFT JOIN T4 with(nolock) ON b.AS_MSNo = T4.ALI_ASNo 91 LEFT JOIN T1 with(nolock) ON b.AS_MSNo = T1.ALDE_MSNo 92 LEFT JOIN dbo.V_PSA_PrjInfo p with(nolock) ON b.AS_ProjectNo=p.PrjCode 93 WHERE b.AS_State = 1 94 and (p.PrjTypeID NOT IN ( 710, 711 ) or p.PrjTypeID is null) 95 and (p.PrjStatus IN ( 6, 10 ) or p.PrjStatus is null ) 96 '+@and + ')' 97 98 99 set @pageStr = replace(@queryStr,'"','''') 100 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)) 101 102 103 DECLARE @strCountSql NVARCHAR(max) 104 SET @strCountSql = @queryStr +' Select @RecordCount = count(*) FROM T2 ' 105 EXEC sp_executesql @strCountSql, N'@RecordCount int OUTPUT', @totalCount OUTPUT 106 107 execute (@endStr) 108 109 110 END 111 112 113 114 115 116 117 118 119 GO
此文仅作用于学习记录，之前写的为一个视图，在功能中又在外面嵌套各种条件进行查询，相当于查询所有数据后再进行分页，速度很慢，特别耗费服务器cpu资源；
把一个视图修改为两个存储过程，一个分页，一个查询所有；调用代码如下：
1 #region 优化为存储过程 20190104 2 var parameters = new List<System.Data.SqlClient.SqlParameter>(); 3 parameters.Add(new System.Data.SqlClient.SqlParameter("@filterCondition", sql)); 4 parameters.Add(new System.Data.SqlClient.SqlParameter("@pageIndex", pageIndex)); 5 parameters.Add(new System.Data.SqlClient.SqlParameter("@pageSize", pageSize)); 6 SqlParameter outParameter = new SqlParameter("@totalCount", SqlDbType.Int, 8); 7 outParameter.Direction = ParameterDirection.Output; 8 parameters.Add(outParameter); 9 var lstAssets = db.Database.SqlQuery<AssetAlterInfo>("exec P_EAM_GetAlterAssetPage @filterCondition, @pageIndex, @pageSize, @totalCount out", parameters.ToArray()).ToList(); 10 count = Convert.ToInt32(outParameter.Value == DBNull.Value ? 0 : outParameter.Value);