sql中存储过程出现的问题,请高手帮帮忙吧

qq_27320995 2015-05-06 05:05:11
这个一段从网上找来的存储过程实现分页功能,我想增加几个参数来传递,却总是出现问题
USE [db_PersonalData]
GO
/****** Object: StoredProcedure [dbo].[sp_Page_GetOrder] Script Date: 2015/5/6 15:55:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_Page_GetOrder]
@startIndex int,
@endIndex int,
@strSql varchar(200),
@strId varchar(20),
@strTableName varchar(30)
as
if @strSql=''
with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders)
select * from table_temp where rowIndex between @startIndex and @endIndex

else

with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders)
select * from table_temp where rowIndex between @startIndex and @endIndex
我想在select * from table_temp where rowIndex between @startIndex and @endIndex
这个里面 加入一个条件参数and @strSql却出现了“在应使用条件的上下文(在 'AND' 附近)中指定了非布尔类型的”
问题
各个大侠请帮帮忙应该怎样写???
...全文
206 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_27320995 2015-05-06
  • 打赏
  • 举报
回复
可以了,不过''' + @strId + '''这个要去掉两个''。真是太感谢了
在路上_- 2015-05-06
  • 打赏
  • 举报
回复
试试

if @strSql=''
with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders)
select * from table_temp where rowIndex between @startIndex and @endIndex

else
begin
  set @strSql='
  with table_temp as (select row_number() over(order by '''
  + @strId + 
  ''') as rowIndex,* from Orders)
  select * from table_temp where rowIndex between '
  + LTRIM(@startIndex) + 
  ' and '
  + LTRIM(@endIndex) +
  ' and ' + @strSql
  exec(@strSql)
end
qq_27320995 2015-05-06
  • 打赏
  • 举报
回复
@strSql 我是当条件语句参数的 如果用动态语句写的话会出现 @startIndex和@endIndex无法变成整形的
在路上_- 2015-05-06
  • 打赏
  • 举报
回复
如果参数可以对应到列名,就可以 if @strSql='' with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders) select * from table_temp where rowIndex between @startIndex and @endIndex else with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders) select * from table_temp where rowIndex between @startIndex and @endIndex and 列名=@strSql --或 列名>@strSql 等其他操作符
在路上_- 2015-05-06
  • 打赏
  • 举报
回复
if @strSql='' with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders) select * from table_temp where rowIndex between @startIndex and @endIndex else begin set @strSql=' with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders) select * from table_temp where rowIndex between @startIndex and @endIndex and ' + @strSql exec(@strSql) end
在路上_- 2015-05-06
  • 打赏
  • 举报
回复
难道是这个意思: if @strSql='' with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders) select * from table_temp where rowIndex between @startIndex and @endIndex else set @strSql=' with table_temp as (select row_number() over(order by @strId) as rowIndex,* from Orders) select * from table_temp where rowIndex between @startIndex and @endIndex and ' + @strSql exec(@strSql)
Neo_whl 2015-05-06
  • 打赏
  • 举报
回复
and 左右必须是一个布尔类型的表达式,只有 and @sqlStr肯定不行,可以是 and @sqlStr!='' ,不知道你的存储过程功能,只能说语法通过
shoppo0505 2015-05-06
  • 打赏
  • 举报
回复
这个不行,建议将@strSql中各条件作为参数输入

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧