set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--------------------------
--分页获取新闻信息
--@intPageIndex int 当前页码
--@intPageSize int 每页行数
--@chvWhere varchar(80) 查询条件
--@chvOrderBy varchar(80) 排序条件
--------------------------ALTER PROC [dbo].[prCay_GetPageNews]
@intPageIndex int, --当前页码
@intPageSize int, --每页行数
@chvWhere varchar(80), --查询条件
@chvOrderBy varchar(80) --排序条件
ASDECLARE @chvSQL varchar(4000)
DECLARE @chvSelect varchar(80)
DECLARE @chvOrder varchar(80) ---判断是否查询
IF CHARINDEX('where',@chvWhere) <> 0
BEGIN
SET @chvSelect = @chvWhere
END
ELSE
BEGIN
SET @chvSelect = ''
END --判断是否排序
IF CHARINDEX('order by',@chvOrderBy) <> 0
BEGIN
SET @chvOrder = @chvOrderBy
END
ELSE
BEGIN
SET @chvOrder = ''
END SET @chvSQL = 'SELECT TOP CAST('''+@intPageSize+''' AS INT [RId],[Type],[Name],[WriteTime]
FROM dbo.[News]
WHERE RId NOT IN
(SELECT TOP CAST('''+(@intPageIndex - 1) * @intPageSize+''' AS INT RId
FROM dbo.[News] '+@chvWhere+' '+@chvOrderBy+')'
+' AND ' +SUBSTRING(@chvWhere,6,LEN(@chvWhere))+' '
+@chvOrderBy+'' EXEC(@chvSQL)
GO--prCay_GetPageNews 1,2,'WHERE Type > 10','ORDER BY Name ASC'
消息 245,级别 16,状态 1,过程 prCay_GetPageNews,第 44 行
在将 varchar 值 'SELECT TOP CAST('' 转换成数据类型 int 时失败。
救正解
set QUOTED_IDENTIFIER ON
go
--------------------------
--分页获取新闻信息
--@intPageIndex int 当前页码
--@intPageSize int 每页行数
--@chvWhere varchar(80) 查询条件
--@chvOrderBy varchar(80) 排序条件
--------------------------ALTER PROC [dbo].[prCay_GetPageNews]
@intPageIndex int, --当前页码
@intPageSize int, --每页行数
@chvWhere varchar(80), --查询条件
@chvOrderBy varchar(80) --排序条件
ASDECLARE @chvSQL varchar(4000)
DECLARE @chvSelect varchar(80)
DECLARE @chvOrder varchar(80) ---判断是否查询
IF CHARINDEX('where',@chvWhere) <> 0
BEGIN
SET @chvSelect = @chvWhere
END
ELSE
BEGIN
SET @chvSelect = ''
END --判断是否排序
IF CHARINDEX('order by',@chvOrderBy) <> 0
BEGIN
SET @chvOrder = @chvOrderBy
END
ELSE
BEGIN
SET @chvOrder = ''
END SET @chvSQL = 'SELECT TOP CAST('''+@intPageSize+''' AS INT [RId],[Type],[Name],[WriteTime]
FROM dbo.[News]
WHERE RId NOT IN
(SELECT TOP CAST('''+(@intPageIndex - 1) * @intPageSize+''' AS INT RId
FROM dbo.[News] '+@chvWhere+' '+@chvOrderBy+')'
+' AND ' +SUBSTRING(@chvWhere,6,LEN(@chvWhere))+' '
+@chvOrderBy+'' EXEC(@chvSQL)
GO--prCay_GetPageNews 1,2,'WHERE Type > 10','ORDER BY Name ASC'
消息 245,级别 16,状态 1,过程 prCay_GetPageNews,第 44 行
在将 varchar 值 'SELECT TOP CAST('' 转换成数据类型 int 时失败。
救正解
cast('' as int)
cast('a' as int)你可以用ISNUMERIC判断一下.ISNUMERIC
确定表达式是否为一个有效的数字类型。语法
ISNUMERIC ( expression )参数
expression要计算的表达式。返回类型
int注释
当输入表达式得数为一个有效的整数、浮点数、money 或 decimal 类型,那么 ISNUMERIC 返回 1;否则返回 0。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。示例
A. 使用 ISNUMERIC
下面的示例返回 1,这是因为 zip 列包含有效的数值。 USE pubs
SELECT ISNUMERIC(zip)
FROM authors
GOB. 使用 ISNUMERIC 和 SUBSTRING
下面的示例对于 titles 表中的所有书名都返回 0,这是因为没有一个书名是有效的数值。USE pubs
GO
-- Because the title column is all character data, expect a result of 0
-- for the ISNUMERIC function.
SELECT SUBSTRING(title, 1, 15) type, price, ISNUMERIC(title)
FROM titles
GO下面是结果集:type price
--------------- -------------------------- -----------
The Busy Execut 19.99 0
Cooking with Co 11.95 0
You Can Combat 2.99 0
Straight Talk A 19.99 0
Silicon Valley 19.99 0
The Gourmet Mic 2.99 0
The Psychology (null) 0
But Is It User 22.95 0
Secrets of Sili 20.00 0
Net Etiquette (null) 0
Computer Phobic 21.59 0
Is Anger the En 10.95 0
Life Without Fe 7.00 0
Prolonged Data 19.99 0
Emotional Secur 7.99 0
Onions, Leeks, 20.95 0
Fifty Years in 11.95 0
Sushi, Anyone? 14.99 0 (18 row(s) affected)
怎么看都感觉少括号呢
set QUOTED_IDENTIFIER ON
go
--------------------------
--分页获取新闻信息
--@intPageIndex int 当前页码
--@intPageSize int 每页行数
--@chvWhere varchar(80) 查询条件
--@chvOrderBy varchar(80) 排序条件
--------------------------ALTER PROC [dbo].[prCay_GetPageNews]
@intPageIndex int, --当前页码
@intPageSize int, --每页行数
@chvWhere varchar(80), --查询条件
@chvOrderBy varchar(80) --排序条件
ASDECLARE @chvSQL varchar(4000)
DECLARE @chvSelect varchar(80)
DECLARE @chvOrder varchar(80) ---判断是否查询
IF CHARINDEX('where',@chvWhere) <> 0
BEGIN
SET @chvSelect = @chvWhere
END
ELSE
BEGIN
SET @chvSelect = ''
END --判断是否排序
IF CHARINDEX('order by',@chvOrderBy) <> 0
BEGIN
SET @chvOrder = @chvOrderBy
END
ELSE
BEGIN
SET @chvOrder = ''
END SET @chvSQL = 'SELECT TOP CAST('''+@intPageSize+''' AS INT) [RId],[Type],[Name],[WriteTime]
FROM dbo.[News]
WHERE RId NOT IN
(SELECT TOP CAST('''+(@intPageIndex - 1) * @intPageSize+''' AS INT) RId
FROM dbo.[News] '+@chvWhere+' '+@chvOrderBy+')'
+' AND ' +SUBSTRING(@chvWhere,6,LEN(@chvWhere))+' '
+@chvOrderBy+'' EXEC(@chvSQL)
GO--prCay_GetPageNews 1,2,'WHERE Type > 10','ORDER BY Name ASC'希望能够得到具体点的 答复 谢谢
用ISNUMERIC函数判断下是不是有不能转化为int的字符
Print也不行~!········
FROM dbo.[News]
WHERE RId NOT IN
(SELECT TOP '+ltrim((@intPageIndex-1) * @intPageSize)+' RId
FROM dbo.[News] '+@chvWhere+' '+@chvOrderBy+')'
+' AND ' +SUBSTRING(@chvWhere,6,LEN(@chvWhere))+' '
+@chvOrderBy+''
在TOP后面使用变量
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2008-01-02 广东深圳)
*/--SQL SERVER 2005 的写法
use adventureworks
goDECLARE @Percentage int
SET @Percentage = 1
SELECT TOP (@Percentage) PERCENT
Name
FROM Production.Product
ORDER BY Name/*
Name
----------------------
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
BB Ball Bearing
Bearing Ball
Bike Wash - Dissolver(6 行受影响)
*/-----------------------------------
--SQL SERVER 2000 的写法
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = 'select top ' + cast(@num as char) + ' * from a'
exec(@sql)drop table a
/*
id
-----------
1
2
*/
@intPageSize int, --每页行数
@chvWhere varchar(80), --查询条件
@chvOrderBy varchar(80) --排序条件·········--prCay_GetPageNews 1,2,'WHERE Type > 10','ORDER BY Name ASC'
看不出,还是另有说法???
FROM dbo.[News]
WHERE RId NOT IN
(SELECT TOP '+ltrim((@intPageIndex - 1) * @intPageSize+)+' RId
FROM dbo.[News] '+@chvWhere+' '+@chvOrderBy+')'
+' AND ' +SUBSTRING(@chvWhere,6,LEN(@chvWhere))+' '
+@chvOrderBy+''
--这样就OK
ALTER PROC [dbo].[prCay_GetPageNews]
@intPageIndex int, --当前页码
@intPageSize int, --每页行数
@chvWhere varchar(80), --查询条件
@chvOrderBy varchar(80) --排序条件
AS
/*
declare @intPageIndex int, --当前页码
@intPageSize int, --每页行数
@chvWhere varchar(80), --查询条件
@chvOrderBy varchar(80) --排序条件select @intPageIndex = 1,@intPageSize=2,
@chvWhere = 'WHERE Type > 10',
@chvOrderBy = 'ORDER BY Name ASC'
*/
DECLARE @chvSQL varchar(4000)
DECLARE @chvSelect varchar(80)
DECLARE @chvOrder varchar(80) ---判断是否查询
IF CHARINDEX('where',@chvWhere) <> 0
BEGIN
SET @chvSelect = @chvWhere
END
ELSE
BEGIN
SET @chvSelect = ''
END --判断是否排序
IF CHARINDEX('order by',@chvOrderBy) <> 0
BEGIN
SET @chvOrder = @chvOrderBy
END
ELSE
BEGIN
SET @chvOrder = ''
END SET @chvSQL = 'SELECT TOP '+ltrim(@intPageSize)+' [RId],[Type],[Name],[WriteTime]
FROM dbo.[News]
WHERE RId NOT IN
(SELECT TOP '+ltrim((ltrim(@intPageIndex) - 1) * ltrim(@intPageSize))+' RId
FROM dbo.[News] '+@chvWhere+' '+@chvOrderBy+')'
+' AND ' +SUBSTRING(@chvWhere,6,LEN(@chvWhere))+' '
+@chvOrderBy+''
EXEC(@chvSQL)