字符串有单引号要用双引号set @strWhere='Publish=1 and CID='+rtrim(str(@CID))+' and Type=0 and JName like ''%'+@JName+'''%' --用rtrim转换一下,或用cast/convert
解决方案 »
- SQL函数中有LIKE查询的问题
- 谁来帮我解释一下: select 1 &1为什么会得1?
- 远程访问数据库
- 求一本深入学习Sql Server书
- 系统启动驱动 服务错误
- 数据库的读写问题,在线等,谢谢!
- 请教对数十亿条数据的表更新字段值的解决思路
- 如何实现查询一个表中第10到20条记录?
- 怎么样枚举局域网上活动的SQL Server(或其它)数据库服务器
- 如何把一张表里一个字段的内容复制到您一个字段里去?
- SELECT mid=IDENTITY(int,1,1),* INTO #i FROM inserted 什么意思
- sqlserver2000,如何用sql语句实现用一个已存在的表创建另一个表和如何判断一个表是否存在?
and JName like ''%'+ltrim(@JName)+'%'' '
@title nvarchar(63),
@category int,
@PageIndex int=1,
@orderby bit=1
AS
declare @strWhere varchar(2000)
--set @strWhere='Publish=1 and CID='+rtrim(str(@CID))+' and Type=0 and JName like ''%'+@JName+'''%' --用rtrim转换一下,或用cast/convert
set @strWhere='category='+ltrim(str(@category))+' and title like ''%'+ltrim(@title)+'''%' --用rtrim转换一下,或用cast/convert
exec GetRecordFromPage'products','id',16,@PageIndex,@orderby,@strWhereALTER PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName nvarchar(63), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')' set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
endexec (@strSQL)
alter PROCEDURE sp_getproducts
@title nvarchar(63),
@category int,
@PageIndex int=1,
@orderby bit=1
AS
declare @strWhere varchar(2000)
--set @strWhere='Publish=1 and CID='+rtrim(str(@CID))+' and Type=0 and JName like ''%'+@JName+'''%' --用rtrim转换一下,或用cast/convert
set @strWhere='category='+ltrim(str(@category))+' and title like ''%'+ltrim(@title)+'%''' --用rtrim转换一下,或用cast/convertexec GetRecordFromPage 'products','id',16,@PageIndex,@orderby,@strWhere