首页 新闻 赞助 找找看

sqlserver 存储过程 临时表无法排序,求各位大神解释

0
悬赏园豆:10 [已解决问题] 解决于 2013-12-25 10:10
  1 USE [HLT_HIEP_V2]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[master_querySupplierCertificate]    Script Date: 06/08/2013 10:54:22 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 ALTER procedure [dbo].[master_querySupplierCertificate]
  9     @OrganizationDomain          NVARCHAR(20),    
 10     @PartnerId                nvarchar(20),
 11     @CertificateId            nvarchar(20),
 12     @CertificateName          nvarchar(80),
 13     @CertificateType          int,
 14     @StartValidDate           datetime,
 15     @EndValidDate             datetime,
 16     @CertificatePhoto         nvarchar(128),    
 17     @Markers                  int,    
 18     @StartCreateDate          datetime,
 19     @EndCreateDate              datetime,
 20     @PageIndex                  int,
 21     @PageSize                  int,
 22     @SortField                  nvarchar(20),
 23     @SortMethod                  nvarchar(20)    
 24 as
 25 begin
 26     DECLARE @PageLowerBound int
 27     DECLARE @PageUpperBound int
 28     DECLARE @TotalRecords   int
 29     SET @PageLowerBound = @PageSize * @PageIndex
 30     SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
 31     --设置排序字段和规则
 32     IF(@SortField IS NULL OR @SortMethod IS NULL OR @SortField='' OR @SortMethod='')
 33     BEGIN
 34         SET @SortField = 'PartnerId'
 35         SET @SortMethod = 'DESC'
 36     END
 37     --拼SQL 按条件排序查询
 38     DECLARE @SqlStr nvarchar(1000)
 39     SET @SqlStr=''
 40     
 41     DECLARE @OrganizationProviderId uniqueidentifier
 42      IF(@OrganizationDomain IS NOT NULL AND @OrganizationDomain<>'' AND @OrganizationDomain<>'%' AND @OrganizationDomain<>'%%')  
 43     BEGIN
 44         SELECT @OrganizationProviderId=OrganizationProviderId FROM system_Organizations WHERE OrganizationDomain = @OrganizationDomain
 45         if(@OrganizationProviderId is null) return -1
 46     END
 47     
 48     IF(@OrganizationProviderId IS NOT NULL)  
 49         SET @SqlStr=@SqlStr+'OrganizationProviderId = '''+CONVERT(nvarchar(50), @OrganizationProviderId, 50) + ''' AND ' 
 50     IF(@PartnerId IS NOT NULL AND @PartnerId<>'' AND @PartnerId<>'%' AND @PartnerId<>'%%')  
 51         SET @SqlStr+='PartnerId like ''%'+@PartnerId + '%'' AND ' 
 52     IF(@CertificateId IS NOT NULL AND @CertificateId<>'' AND @CertificateId<>'%' AND @CertificateId<>'%%')  
 53         SET @SqlStr+='CertificateId like ''%'+@CertificateId + '%'' AND ' 
 54     IF(@CertificateName IS NOT NULL AND @CertificateName<>'' AND @CertificateName<>'%' AND @CertificateName<>'%%')  
 55         SET @SqlStr+='CertificateName like ''%'+@CertificateName + '%'' AND '    
 56     IF(@CertificateType IS NOT NULL )  
 57         SET @SqlStr+='CertificateType = '''+@CertificateType + ''' AND '      
 58     IF(@StartValidDate IS NOT NULL)
 59         SET @SqlStr += 'StartValidDate >= '''+CONVERT(varchar(50), @StartValidDate, 21) + ''' AND '     
 60     IF(@EndValidDate IS NOT NULL)  
 61         SET @SqlStr+='EndValidDate <= '''+CONVERT(varchar(50), @EndValidDate, 21) + ''' AND ' 
 62     IF(@CertificatePhoto IS NOT NULL AND @CertificatePhoto<>'' AND @CertificatePhoto<>'%' AND @CertificatePhoto<>'%%')  
 63         SET @SqlStr+='CertificatePhoto like ''%'+@CertificatePhoto + '%'' AND ' 
 64      IF(@Markers IS NOT NULL AND @Markers<>'' AND @Markers<>'%' AND @Markers<>'%%')  
 65         SET @SqlStr+='Markers = '''+@Markers + ''' AND '          
 66     IF(@StartCreateDate IS NOT NULL)
 67         SET @SqlStr += 'CreateDate >= '''+CONVERT(varchar(50), @StartCreateDate, 21) + ''' AND ' 
 68     IF(@EndCreateDate IS NOT NULL)  
 69         SET @SqlStr+='CreateDate <= '''+CONVERT(varchar(50), @EndCreateDate, 21) + ''' AND '     
 70     
 71     IF(RIGHT(@SqlStr,4)='AND ') SET @SqlStr = LEFT(@SqlStr,LEN(@SqlStr)-4)        
 72     IF(@SqlStr<>'') SET @SqlStr=' WHERE '+@SqlStr
 73     SET @SqlStr= 'SELECT OrganizationProviderId,PartnerId,CertificateId FROM master_SupplierCertificate '+@SqlStr    
 74     SET @SqlStr= @SqlStr + ' ORDER BY '+ @SortField + ' ' + @SortMethod
 75     
 76     CREATE TABLE #sortInformation
 77     (
 78         IndexId int IDENTITY (0, 1) NOT NULL,
 79         [OrganizationProviderId]  uniqueidentifier NOT NULL,
 80         PartnerId                 nvarchar(20),
 81         CertificateId             nvarchar(20)
 82     )
 83     INSERT INTO #sortInformation([OrganizationProviderId],PartnerId,CertificateId) 
 84     EXEC(@SqlStr)    
 85     
 86     SET @TotalRecords = @@ROWCOUNT
 87     
 88     SELECT o.OrganizationProviderId
 89       ,o.[PartnerId]
 90       ,o.[CertificateId]
 91       ,[CertificateName]
 92       ,[CertificateType]
 93       ,[StartValidDate]
 94       ,[EndValidDate]
 95       ,[CertificatePhoto]
 96       ,[Remark]
 97       ,[Markers]
 98   FROM [HLT_HIEP_V2].[dbo].[master_SupplierCertificate] AS o, #sortInformation AS s
 99     WHERE    o.OrganizationProviderId=s.OrganizationProviderId 
100     and o.PartnerId = s.PartnerId 
101     and o.CertificateId = s.CertificateId
102     AND  s.IndexId 
103     BETWEEN convert(varchar(10), @PageLowerBound) 
104     and  convert(varchar(10), @PageUpperBound) 
105    order by  @SortField + ' ' + @SortMethod 
106         
107     RETURN @TotalRecords
108 end
View Code

 

在最后边对临时表的order by 操作的时候 ,发现无法排序了 ,求解释 。谢谢

梁子se7en的主页 梁子se7en | 初学一级 | 园豆:177
提问于:2013-06-08 11:00
< >
分享
最佳答案
0

你已经知道临时表中的列名了, 想传入排序条件

可以这么写

ORDER BY
CASE WHEN @SortField = 'PartnerId'
AND @SortMethod = 'ASC'
THEN PartnerId END ASC,
CASE WHEN @SortField = 'PartnerId'
AND @SortMethod != 'ASC'
THEN PartnerId END DESC,

CASE WHEN @SortField = 'CertificateId'
AND @SortMethod = 'ASC'
THEN CertificateId END ASC,
CASE WHEN @SortField = 'CertificateId'
AND @SortMethod != 'ASC'
THEN CertificateId END DESC
收获园豆:10
gunsmoke | 老鸟四级 |园豆:3592 | 2013-06-08 13:14

是这样 ,知道列名了,但是不知道传入的是哪一列 ,如果要把每一列都从新写的话 ,我觉得应该还有更好的方案吧 。就是之所以这么写是因为不知道哪列出入作为排序的字段

梁子se7en | 园豆:177 (初学一级) | 2013-06-08 13:22

@梁子se7en:  要么就只能用 dynamic sql了  只能动态拼接最后的query

gunsmoke | 园豆:3592 (老鸟四级) | 2013-06-08 13:33
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册