我在SQL SERVER2000下写一个存储过程中有一段select top (@PageSize) * from。。其中@PageSize为INT型输入参数,但是在2000下无法通过,(@PageSize) 改为实际数字比如10就能通过,但是select top (@PageSize) * from。。在2005就能通过,,
但是公司是基于SQL SERVER 2000的,那我该怎么办呢????
但是公司是基于SQL SERVER 2000的,那我该怎么办呢????
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([id] int,[value] int)
insert #tb
select 1,90 union all
select 1,60 union all
select 2,90 union all
select 2,90
go
declare @i int =3
declare @sql varchar(2000)
set @sql='select top '+ltrim(@i)+' * from #tb'
exec (@sql)
EXEC( 'SELECT TOP '+@PageSize+' * FROM ...')
set @i=3
declare @sql varchar(2000)
set @sql='select top '+ltrim(@i)+' * from salesInfos'
exec (@sql)
这样可以通过,就是变量不能设置默认值
where row_index < @PageSize
就报错(SQL SERVER 2000环境)
消息 139,级别 15,状态 1,第 0 行
不能向局部变量赋予默认值。
set @pagesize=4
exec ('select top '+convert(varchar,@pagesize)+' * from tb')
@StartTime dateTime,
@EndTime dateTime,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGINSET NOCOUNT ON;select * from
(select top (@PageSize) * from --top (@PageSize)
(select top (@PageSize*@PageNum) * from --top (@PageSize*@PageNum)
(SELECT PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) AS salesTime, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos
WHERE (SUBSTRING(PatternNumBer, 1, 1) =@Type and datediff(day,salesTime,@StartTime)>=0 and datediff(day,salesTime,@EndTime)<=0 )
GROUP BY PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time)) as a
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) asc) as b
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) desc) as c
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) ascEND红色的两段在SQL SERVER2000中报错,因为TOP后无法跟参数,但是按照各位上面说的方法,使用字符串,也不行,我用单引号依次分开局部变量,老是出错,当然,上述存储过程在SQL SERVER2005是正确的
@StartTime dateTime,
@EndTime dateTime,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGINSET NOCOUNT ON;
exec(
'select * from
(select top ('+convert(varchar,@PageSize)+') * from --top (@PageSize)
(select top ('+convert(varchar(@PageSize*@PageNum)+') * from --top (@PageSize*@PageNum)
(SELECT PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) AS salesTime, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos
WHERE (SUBSTRING(PatternNumBer, 1, 1) =@Type and datediff(day,salesTime,@StartTime)>=0 and datediff(day,salesTime,@EndTime)<=0 )
GROUP BY PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time)) as a
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) asc) as b
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) desc) as c
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) asc
')
END
除非在@PageSize前加个ltrim()函数处理好象是可以的,
10楼:
declare @pagesize int
set @pagesize=4
exec ('select top '+convert(varchar,@pagesize)+' * from tb')在SQL SERVER2000下是错的吧,
加不加trim无所谓,T-SQL对空格没要求
declare @pagesize int
set @pagesize=4
exec ('select top '+convert(varchar,@pagesize)+' * from tb')
类似这样的就是报错的,(SQL SERVER2000下)第二,我就是注意到了后面的变量后还是没办法用单引号分开成字符串,不然这么简单我也不问了啊,呵呵
CREATE PROCEDURE ZSalesInfosByTypeDayPage
@StartTime dateTime,
@EndTime dateTime,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGINSET NOCOUNT ON;
declare @sql varchar(8000)
set @sql=
'select * from
(select top '+convert(varchar,@PageSize)+' * from
(select top '+convert(varchar(@PageSize*@PageNum)+' * from
(SELECT PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) AS salesTime, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos
WHERE (SUBSTRING(PatternNumBer, 1, 1) ='''+convert(varchar,@Type)+''' and datediff(day,salesTime,'''+convert(varchar,@StartTime,120)+''')>=0 and datediff(day,salesTime,'''+convert(varchar,@EndTime,120)+''')<=0 )
GROUP BY PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time)) as a
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) asc) as b
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) desc) as c
order by PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time) asc
'
exec(@sql)
请问这里为什么是三对单引号????
set @pagesize=4
exec ('select top '+convert(int,@pagesize)+' * from salesinfos')消息 156,级别 15,状态 1,第 3 行
在关键字 'convert' 附近有语法错误。
20楼的有问题吗?你试过没有哦
你print @sql看看有没有语法错误,加双引号是因为那个变量要表示为字符串拼起来
set @pagesize='A00394'
--declare @sql varchar(200)
--set @sql=select * from salesinfos where patternnumber='''+@Pagesize+'''
exec ('select * from salesinfos where patternnumber='''+Convert(varchar,@Pagesize)+''' and 1=1')结果:
消息 156,级别 15,状态 1,第 5 行
在关键字 'Convert' 附近有语法错误。但是不使用convert就可以
再汗,是exec()括号内不能有convert,用个变量接收就可以啊,参照20楼declare @sql varchar(100)
declare @i int
set @i=1
set @sql=
'select top '+convert(varchar,@i)+ '* from sysobjects'
exec('select top '+convert(varchar,@i)+ '* from sysobjects')---报错
exec(@sql)--OK
exec(@sql)才可以另外,对top后面的参数,如果原始变量时int,那么必须转换为varchar或者加ltrim()也可以通过,
如果你觉得这么写太死板,你可以写一个存储过程,当参数传递进来。
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[ZSalesInfosByTypeDayPage]
@StartTime datetime,
@EndTime datetime,
@PageSize INT=10,
@PageNum INT=1,
@Type varchar(50)='A'AS
BEGIN
SET NOCOUNT ON;
declare @sql varchar(8000)set @sql='select * from
(select top '+convert(varchar,@PageSize)+' * from
(select top '+convert(varchar,(@PageSize*@PageNum))+' * from
(SELECT PatternNumBer, ShopType, Size, Color,CONVERT(datetime, Time) AS SalesTime, SUM(CONVERT(decimal(10, 2), DailySalesVolume)) AS num
FROM dbo.SalesInfos
WHERE (SUBSTRING(PatternNumBer, 1, 1) ='''+convert(varchar,@Type)+''' and datediff(day,CONVERT(datetime, Time),'''+convert(varchar,@StartTime)+''')<=0 and datediff(day,CONVERT(datetime, Time),'''+convert(varchar,@EndTime)+''')>=0 )
GROUP BY PatternNumBer, ShopType, Size, Color, CONVERT(datetime, Time)) as a
order by PatternNumBer, ShopType, Size, Color,SalesTime asc) as b
order by PatternNumBer, ShopType, Size, Color,SalesTime desc) as c
order by PatternNumBer, ShopType, Size, Color,SalesTime asc'
exec(@sql)
END
命令已成功完成。