如题。如果一个存储过程:
CREATE PROCEDURE
AS
select * from
(
select * from T1
union
select * from T2
) out1其中子查询中union几个表不确定的话,整个子查询可以作为一个参数吗?
CREATE PROCEDURE
AS
select * from
(
select * from T1
union
select * from T2
) out1其中子查询中union几个表不确定的话,整个子查询可以作为一个参数吗?
execu(传入的查询语句)。大概就是这样!
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([ID] int, BidId int , BiderName varchar(20), BidPrice int, BidTime datetime)
insert [TB]select
21, 1 ,'sunjoin' , 1 ,'2009-08-28 09:57:00.000' union all select
22 ,1 ,'sunjoin' , 5, '2009-08-28 09:57:00.000' union all select
23 ,1 ,'peter' ,9 ,'2009-08-28 09:57:00.000' union all select
24 ,1 ,'peter' ,10 ,'2009-08-28 09:57:00.000' union all select
25 ,1, 'peter' ,10 ,'2009-08-28 09:57:00.000' union all select
26 ,1, 'sunjoin1' , 2, '2009-08-28 09:57:00.000' union all select
27 ,1, 'sunjoin' , 3 ,'2009-08-31 14:45:05.000' union all select
28 ,1, 'sunjoin1' , 8 ,'2009-08-31 14:49:09.000' union all select
29 ,2, 'sunjoin1' , 98, '2009-08-31 14:49:17.000'
gocreate proc dd
@sql varchar(4000)
as
begin
set @sql='select * from tb where id=24 '+@sql
exec(@sql)
end
go
declare @sql varchar(4000)
set @sql=' union all select * from tb where id=21'
exec dd @sqlID BidId BiderName BidPrice BidTime
----------- ----------- -------------------- ----------- -----------------------
24 1 peter 10 2009-08-28 09:57:00.000
21 1 sunjoin 1 2009-08-28 09:57:00.000(2 行受影响)drop proc dd
7楼已经给的很详细了
@sql varchar(4000)
AS
select * from
(
@sql
) out1 declare @sql
set @sql = [
select * from T1
union
select * from T2
union
select * from T3]
exec dd) out1
类似这种效果,存储过程中的一个子查询作为参数,union之后的语句由外部传入该参数。