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)
查询出的结果有时是一条,有时是多条,这个地方我改如何改,请帮忙???SQL动态生成SQL
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,我想要将查询出来的数据全部显示的
哪个地方使用exits,能说详细点吗???
(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)
试着这样改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)
ASIF @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