Create Procedure [dbo].[pro1]
@ T1 char(20),T2 char(20)
AS
select * from @T1
left @T2
on @T1.id=@T2.id
RETURN
以上表名可以用变量代替吗,如果可以怎么使用?
------解决方案--------------------
大概这样：
/*
CREATE TABLE test1
(id INT )
CREATE TABLE test2
(id INT)
INSERT INTO test1
VALUES(1)
INSERT INTO test2
VALUES(1)
*/
alter Procedure [dbo].[pro1]
@T1 varchar(20),
@T2 varchar(20)
AS
DECLARE @sql varchar(max)
SET @sql='select * from '+ QUOTENAME(@T1) +' left join '+ QUOTENAME(@T2) +' on '+QUOTENAME(@T1)+'.id='+QUOTENAME(@T2) +'.id'
EXEC( @sql)
go
EXEC pro1 'test1','test2'
/*
id id
----------- -----------
1 1
*/
------解决方案--------------------
使用动态语句
CREATE PROCEDURE [dbo].[pro1]
@ T1 CHAR(20),T2 CHAR(20)
AS
EXECUTE('select * from '+@T1+
'left join '+@T2+
'on '+@T1+'.id='+@T2+'.id');
END
------解决方案--------------------
Use dynamic SQL
ALTER PROC [dbo].[Duty_pro_total_times]
-- 存储过程参数
@Tb_duty_kind VARCHAR(50) ,
@Tp_table VARCHAR(50) ,
@from_datetime DATETIME ,
@to_datetime DATETIME ,
@str_days CHAR(20)
AS -- 存储过程语句体
DECLARE @sql VARCHAR(MAX)
SET @sql = '(select Isnull(sum(G1.s_day),0),Isnull(G2.duty_10days,0) ,sum(G3.man_number),
Isnull(sum(G5.s_day1),0),Isnull(sum(G6.s_day2),0) from
(select dep_serial,convert(nvarchar(50),dep_name) as dep_name,H.s_day from dt_dep D
Left Join
(select S.m_id, sum(S.perday) as s_day from
V_s as S
where (S.j_mark=''1'' or S.j_mark=''2'') and
S.RQ>=@from_datetime and RQ<=@to_datetime group by S.m_id ) H
on D.dep_serial=H.m_id) G1
Left Join
(select aa.teams,count(duty_kind) as duty_10days from ' + @Tb_duty_kind
+ ' aa
inner join ' + @Tp_table
+ ' bb on aa.duty_date=bb.RQ where aa.duty_kind='' and
bb.RQ>=@from_datetime and bb.RQ<=@to_datetime group by aa.teams) G2
on G1.dep_serial=G2.teams
left join
(select m_id,man_number from Tb_dep_number_month
where month=@str_days) G3 on G1.dep_serial=G3.m_id
left join
(select dep_serial,convert(nvarchar(50),dep_name) as dep_name,H.s_day1 from dt_dep D
Left Join
(select S.m_id, sum(S.perday) as s_day1 from
V_s as S
where S.j_mark=''1'' and S.RQ>=@from_datetime and RQ<=@to_datetime group by S.m_id ) H
on D.dep_serial=H.m_id) G5
on G1.dep_serial=G5.dep_serial
left join
(select dep_serial,convert(nvarchar(50),dep_name) as dep_name,H.s_day2 from dt_dep D
Left Join
(select S.m_id, sum(S.perday) as s_day2 from
V_s as S
where S.j_mark=''2'' and S.RQ>=@from_datetime and RQ<=@to_datetime group by S.m_id ) H
on D.dep_serial=H.m_id) G6