请教!!

[koukoujiayi]

写一存储过程,不加 ORDER BY很正常,加了ORDER BY 就不行,报错:

代码如下:
ALTER PROCEDURE [dbo].[Pg_YearTotal]
(
@TableName varchar(50), --表名
@ReFieldsStr varchar(50), --DateTime字段名
@OrderString varchar(50)='', --排序字段(不用加order by)
@WhereString varchar(500) =N'' --条件语句(不用加where)
)
AS
BEGIN
Declare @SqlString nvarchar(2000);
set @SqlString=N'(select DATEPART(year,'+ @ReFieldsStr+') AS TheYear,count(*) AS YearCount FROM '+@TableName
IF (@WhereString! = '' or @WhereString!=null)
set @SqlString = @SqlString+' WHERE '+ @WhereString

set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'+ @ReFieldsStr+') ORDER BY ' + @OrderString +'DESC )'
Exec(@SqlString)
END

红色部分生成存储过程没报错!但就是执行不了,例如
Pg_YearTotal 'table','字段1,'字段1','字段1>2005-1-1'
报错:
消息 156，级别 15，状态 1，第 1 行
关键字 'ORDER' 附近有语法错误。

望指教!!不胜感谢!!

[SQL77]

你PRINT试试看是什么

[SQL77]

set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'+ @ReFieldsStr+') ORDER BY ' + @OrderString +' DESC )'
可能是这的问题

[SQL77]

declare @TableName varchar(50)     
SET   @TableName='TB' --表名 
declare @ReFieldsStr varchar(50)
SET     @ReFieldsStr='2009-8-21'   --DateTime字段名 
declare @OrderString varchar(50) 
SET   @OrderString='COL'   --排序字段(不用加order by) 
declare @WhereString varchar(500)  
SET  @WhereString='1=1'  --条件语句(不用加where) ) 

Declare @SqlString nvarchar(2000); 
set @SqlString=N'(select DATEPART(year,'+ @ReFieldsStr+') AS TheYear,count(*) AS YearCount FROM '+@TableName 

set @SqlString = @SqlString+' WHERE '+ @WhereString 

    set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'+ @ReFieldsStr+') ORDER BY ' + @OrderString +' DESC )' 
PRINT @SqlString
/*
(select DATEPART(year,2009-8-21) AS TheYear,count(*) AS YearCount FROM TB WHERE 1=1 GROUP BY DATEPART(year,2009-8-21) ORDER BY COL DESC )

就是少了个空格的问题

[koukoujiayi]

[Quote=引用 1 楼 sql77 的回复:]
你PRINT试试看是什么
[/Quote]
怎么print?
去掉DESC,排序字段和GROUP BY一样也不行!!
如:
.....
set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'+ @ReFieldsStr+')'
set @SqlString = @SqlString + ' ORDER BY DATEPART(year,'+ @ReFieldsStr +'))'

[--小F--]

把Exec(@SqlString) 改成了print @SqlString 就可以检查错误了

[SQL77]

declare @TableName varchar(50)     
SET   @TableName='TB' --表名 
declare @ReFieldsStr varchar(50)
SET     @ReFieldsStr='2009-8-21'   --DateTime字段名 
declare @OrderString varchar(50) 
SET   @OrderString='COL'   --排序字段(不用加order by) 
declare @WhereString varchar(500)  
SET  @WhereString='1=1'  --条件语句(不用加where) ) 

Declare @SqlString nvarchar(2000); 
set @SqlString=N'select DATEPART(year,'''+ @ReFieldsStr+''') AS TheYear,count(*) AS YearCount FROM '+@TableName 

set @SqlString = @SqlString+' WHERE '+ @WhereString 

    set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'''+ @ReFieldsStr+''') ORDER BY ' + @OrderString +' DESC ' 
PRINT @SqlString
/*
select DATEPART(year,'2009-8-21') AS TheYear,count(*) AS YearCount FROM TB WHERE 1=1 GROUP BY DATEPART(year,'2009-8-21') ORDER BY COL DESC 

这样应该行了

[SQL77]

[Quote=引用 4 楼 koukoujiayi 的回复:]
引用 1 楼 sql77 的回复:
你PRINT试试看是什么

怎么print?
去掉DESC,排序字段和GROUP BY一样也不行!!
如:
.....
set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'+ @ReFieldsStr+')' 
set @SqlString = @SqlString + ' ORDER BY DATEPART(year,'+ @ReFieldsStr +'))'
[/Quote]
日期字段上应该要用两个表一个呀,你是字符型的,所以要引号引起来