SQL视图优化改写为存储过程遇到 双引号 单引号问题

核心在于拼接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, 8);
                    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 ? 0 : outParameter.Value);