求帮忙改错

[party620]

if exists(select * from sys.objects where name='up_search')
drop proc up_search
go
create proc up_search 
@type varchar(255),@firm varchar(255),@start int,@end int,@pro varchar(255),@city varchar(255)
as
select * from
(select *,ROW_NUMBER() over (order by ccode)as num from CraneInfo
where Ctype=case when @type<>'-1' then @type else Ctype end
	and Company=case when @firm<>'-1' then @firm else Company end
	and RegionId=case when @pro='-1' then RegionId else
	(select SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) end

) as test where num between @start and  @end

在以上SQL语句中(select SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end)
查询出的结果有时是一条，有时是多条，这个地方我改如何改，请帮忙？？？

[party620]

各位帮帮忙，实在不会啊，

[-Tracy-McGrady-]

随便加个in 啊，max啊，top 1啊，min 啊，行不行？你要说出你的需求

[party620]

需求就是按照要求把所要查询出来的结果都显示在页面上

[party620]

谁来帮帮我啊啊

[---涛声依旧---]

感觉楼主的存储过程没有条理 试着这样改

if exists(select * from sys.objects where name='up_search')
drop proc up_search
go
create proc up_search 
@type varchar(255),@firm varchar(255),@start int,@end int,@pro varchar(255),@city varchar(255)
AS

IF @type<>'-1' AND @firm<>'-1' AND @pro<>'-1' AND @city<>'-1'
	BEGIN
		WITH CTE AS 
		(select *,ROW_NUMBER() over (order by ccode)as num from CraneInfo
		 WHERE Ctype=@type AND Company=@firm 
		 AND RegionId IN (select REGION_ID from SEC_REGION where REGION_ID=@city)
			)
		SELECT * FROM CTE WHERE num between @start and  @end
	END
else
	begin
		SELECT 1 --此处楼主自己写了
	end

[party620]

引用 11 楼 rockyljt 的回复:

按照你的写法，查询结果有两种，一种是所有记录，还有一种是所有条件都不为空的情况，我理解没错吧 但是我要求查询出来的结果不仅仅只两种情况，情况1：查询所有的；情况2：查询所有条件都不为空的记录； 情况3：第一个条件不为空，其余条件可能为空也可能不为空（这种情况下又包含好多情况）；。。。。 可能下面还有好多不同的查询条件；（注释：为空表示全部）

[party620]

期待着解决，先谢谢各位

[茶暖人凉]

打酱油的，飘过

[---涛声依旧---]

如果是多条且随便取一条时，就加上top 1 and RegionId=case when @pro='-1' then RegionId else (select TOP 1 SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) end

[---涛声依旧---]

否则，则改用exits语句了

[party620]

引用 1 楼 rockyljt 的回复:

如果是多条且随便取一条时，就加上top 1 and RegionId=case when @pro='-1' then RegionId else (select TOP 1 SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) end

不能使用top1，我想要将查询出来的数据全部显示的

[party620]

引用 2 楼 rockyljt 的回复:

否则，则改用exits语句了

哪个地方使用exits，能说详细点吗？？？

[---涛声依旧---]

将and RegionId=case when @pro='-1' then RegionId else (select SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) end 改为 and exits(select 1 from ...) 或者改为in and RegionId IN (case when @pro='-1' then RegionId else (select SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) END)

[party620]

引用 5 楼 rockyljt 的回复:

将and RegionId=case when @pro='-1' then RegionId else (select SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) end 改为 and exits(select 1 from ...) 或者改为in and RegionId IN (case when @pro='-1' then RegionId else (select SEC_REGION.REGION_ID from SEC_REGION where REGION_ID=case when @city<>'-1' then @city else REGION_ID end) END)

改为in还是一样的错误，