写一存储过程,不加 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' 附近有语法错误。望指教!!不胜感谢!!
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' 附近有语法错误。望指教!!不胜感谢!!
可能是这的问题
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 )就是少了个空格的问题
去掉DESC,排序字段和GROUP BY一样也不行!!
如:
.....
set @SqlString = @SqlString + ' GROUP BY DATEPART(year,'+ @ReFieldsStr+')'
set @SqlString = @SqlString + ' ORDER BY DATEPART(year,'+ @ReFieldsStr +'))'
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 这样应该行了