首页新闻找找看学习计划

挺奇怪的问题,大家一起来讨论讨论!

0
悬赏园豆:100 [已关闭问题]


--这是一个数据表和一个存储过程 但在执行存储过出现的问题挺奇怪的,不知大家有没有
发现过类似的问题,还希望大家指点指点,在下先行谢过了:
问题如下:
如果存储过程的参数@condition=
house_type = 2  and quyu= 110103 and (price  between 100 and 120)
就会报错的
但是如果存过程的参数@condition=house_type = 2  and quyu= 110107 and erjidiming= 188 and lx=3 and line=2 and (substring(fx,0,1)=5) and (price  between 80 and 100) and (area  between 70 and 90 )
又是可以的。。
并且我打印出来的sql语句是可以执行的
下面是执行存储过程的语句:
USE [aa]
GO

DECLARE    @return_value int,
        @rowCount int

SELECT    @rowCount = 0

EXEC    @return_value = [dbo].[cndoup_GetPageOfRecords]
        @pageSize = 2,
        @currentPage = 2,
        @condition = N'house_type = 2  and quyu= 110107 and erjidiming= 188 and lx=3 and line=2 and (substring(fx,0,1)=5) and (price  between 80 and 100) and (area  between 70 and 90 )',
        @ascColumn = N'H_id',
        @bitOrderType = false,
        @pkColumn = N'H_id',
        @rowCount = @rowCount OUTPUT

SELECT    @rowCount as N'@rowCount'

SELECT    'Return Value' = @return_value

GO
下面是数据库,数据库表,存储过程 ,还请大家run一下:


create database aa
go
use aa
go
CREATE TABLE [dbo].[h_baseInfo](
[h_id] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[title] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[address] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[price] [money] NULL,
[area] [decimal](18, 0) NULL,
[areaname] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[house_struct] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[layer] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[pic_link] [varchar](40) COLLATE Chinese_PRC_CI_AS NULL,
[state] [bigint] NULL,
[updatetime] [datetime] NULL,
[username] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[house_type] [bigint] NULL,
[P_num] [int] NULL,
[isCommissioned] [bit] NULL,
[isrecommeded] [bit] NULL,
[istop] [bit] NULL,
[erjidiming] [bigint] NULL,
[shangquan] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[browse_count] [bigint] NULL,
[fx] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[lx] [int] NULL,
[line] [int] NULL,
[towordId] [int] NULL,
[useId] [int] NULL,
[propertyId] [bigint] NULL,
[fitmentId] [bigint] NULL,
[zbptss] [varchar](150) COLLATE Chinese_PRC_CI_AS NULL,
[fwptss] [varchar](150) COLLATE Chinese_PRC_CI_AS NULL,
[house_year] [int] NULL,
[traffic] [varchar](150) COLLATE Chinese_PRC_CI_AS NULL,
[payment_form] [bigint] NULL,
[submitTime] [datetime] NULL,
[rigth_id] [int] NULL,
[reserve_time] [datetime] NULL,
[quyu] [bigint] NULL
)
go

create proc [dbo].[cndoup_GetPageOfRecords]
@pageSize int = 20, --分页大小
@currentPage int , --第几页
@condition varchar(1000) = '1=1', --查询条件, 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50), --主键名称
@rowCount int output

AS
BEGIN --存储过程开始
DECLARE @strTemp nvarchar(300)
DECLARE @strSql nvarchar(4000) --该存储过程最后执行的语句
DECLARE @strOrderType nvarchar(1000) --排序类型语句 (order by column asc或者order by column desc)
declare @myrowCount int --总记录数数
declare @strsql1 nvarchar(100) --strsql1
declare @usetime datetime
declare @cnt int, @param nvarchar(4000)
set @usetime=getdate()
BEGIN
IF @bitOrderType = 1 --降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = ' <(SELECT min'
END
ELSE --升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END

IF @currentPage = 1 --第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' * FROM H_baseInfo WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' * FROM H_baseInfo'+@strOrderType
END

ELSE -- 其他页
BEGIN
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' * FROM H_baseInfo WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM H_baseinfo where '+@condition+@strOrderType+') AS TabTemp)order by istop desc'
END
END
set @strsql1 ='select @count=count('+@pkColumn+') from H_baseInfo where '+@condition
set @param='@count int output'
exec sp_executesql @strsql1, @param, @cnt output

set @rowCount=@cnt
print @strSql


exec (@strSql)
select datediff(ms,@usetime,getdate())
END

 

yesmadom的主页 yesmadom | 初学一级 | 园豆:100
提问于:2010-01-26 10:24
< >
分享
其他回答(1)
0

复制出来竟然都是一行....

Jared.Nie | 园豆:1940 (小虾三级) | 2010-01-26 15:28
0
  declare @strsql1    nvarchar(100)    
这句改成
  declare @strsql1    nvarchar(2000)    
@strsql1过短
你的出错不在你print的语句,在这句
exec sp_executesql @strsql1, @param, @cnt output
邀月 | 园豆:25200 (高人七级) | 2010-01-29 14:56
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册