CREATE PROCEDURE [dbo].[查看最大库存货品] @view_name varchar(100) AS exec ('select * from'+ @view_name +' where 货品类别=''男鞋'' and 数量=(select max(数量) from'+ @view_name+' where 货品类别=''男鞋'') union select * from'+ @view_name +' where 货品类别=''女鞋'' and 数量=(select max(数量) from'+ @view_name +' where 货品类别=''女鞋'') -... --太长了,此处省略若干 ')
--调用示例 exec [查看最大库存货品] 'view2'
楼主没有必要写那么多中间变量的,直接那些中间变量@a什么的替换成sql语句也是可以的
declare @sql varchar(8000) declare @view varchar(20)set @sql = '' set @view = 'view1'...@sql = 'select a.* from '+@view+' a inner join (select 货品类别,max(数量) as 数量 from '+@view+' group by 货品类别) b on a.货品类别 = b.货品类别 and a.数量 = b.数量 where a.货品类别 in( ... )'exec(@sql)
select a.* from view1 a, (select 货品类别,max(isnull(数量,0)) as '数量' from view1 group by 货品类别) b where a.ltrim(rtrim(货品类别))=b.ltrim(rtrim(货品类别)) order by 货品类别
create proc pr_zdkc declare @货品类别 char(40) ='%' as --可以手工输入货品类别,也可以默认所有类别的最大库存 begin select a.* from view1 a, (select 货品类别,max(isnull(数量,0)) as '数量' from view1 order by 货品类别) b where a.ltrim(rtrim(货品类别))=b.ltrim(rtrim(货品类别)) and a.货品类别 like (@货品类别+'%') order by 货品类别 end
CREATE PROCEDURE [dbo].[查看最大库存货品]
@view_name varchar(100)
AS exec ('select * from'+ @view_name +' where 货品类别=''男鞋''
and 数量=(select max(数量) from'+ @view_name+' where 货品类别=''男鞋'')
union
select * from'+ @view_name +' where 货品类别=''女鞋''
and 数量=(select max(数量) from'+ @view_name +' where 货品类别=''女鞋'')
-... --太长了,此处省略若干
')
exec [查看最大库存货品] 'view2'
declare @view varchar(20)set @sql = ''
set @view = 'view1'...@sql = 'select a.* from '+@view+' a inner join (select 货品类别,max(数量) as 数量 from '+@view+' group by 货品类别) b on a.货品类别 = b.货品类别 and a.数量 = b.数量 where a.货品类别 in( ... )'exec(@sql)
(select 货品类别,max(isnull(数量,0)) as '数量' from view1 group by 货品类别) b
where a.ltrim(rtrim(货品类别))=b.ltrim(rtrim(货品类别))
order by 货品类别
declare @货品类别 char(40) ='%'
as
--可以手工输入货品类别,也可以默认所有类别的最大库存
begin
select a.* from view1 a,
(select 货品类别,max(isnull(数量,0)) as '数量' from view1 order by 货品类别) b
where a.ltrim(rtrim(货品类别))=b.ltrim(rtrim(货品类别))
and a.货品类别 like (@货品类别+'%')
order by 货品类别
end