--示例
create proc p_qqy
@sTableName varchar(20)='tInOrder',
sBillNo varchar(20)='2004-7-4',
@@sBillNo varchar(20) out
as
declare @s nvarchar(4000)
set @s='
select @sBillNo=
(select max(billNo) from ['+@sTableName+'] where billNo in
(select billNo from tInOrder where left(BillNo,8)=@sBiLLNo))'
exec sp_executesql @s
,N'sBillNo varchar(20),@sBillNo varchar(20) out'
,sBillNo,@sBillNo out
go
create proc p_qqy
@sTableName varchar(20)='tInOrder',
sBillNo varchar(20)='2004-7-4',
@@sBillNo varchar(20) out
as
declare @s nvarchar(4000)
set @s='
select @sBillNo=
(select max(billNo) from ['+@sTableName+'] where billNo in
(select billNo from tInOrder where left(BillNo,8)=@sBiLLNo))'
exec sp_executesql @s
,N'sBillNo varchar(20),@sBillNo varchar(20) out'
,sBillNo,@sBillNo out
go
@sTableName varchar(20),
sBillNo varchar(20),
dBillDate DateTime
As
--select @sTableName='tInOrder' --改为参数
--select @dBillDate='2004-7-4' --改为参数
select @sBillNo=
(select max(billNo) from tInOrder where billNo in
(select billNo from tInOrder where left(BillNo,8)=@sBiLLNo))
declare @sBillNo varchar(20)
exec p_qry 'tInOrder','2004-7-4',@sBillNo out
select @sBillNo
@sTableName varchar(20),
sBillNo varchar(20),
dBillDate DateTime
As
--select @sTableName='tInOrder' --改为参数
--select @dBillDate='2004-7-4' --改为参数
select @sBillNo=
(select max(billNo) from tInOrder where billNo in
(select billNo from tInOrder where left(BillNo,8)=@sBiLLNo))我的意思是想让上面查询的tInOrder成为一个形参,即变量,请问如何做好?
(@sTableName varchar(20),
@sBillNo varchar(20))
as
Declare @sql varchar(2000)
select @sql = 'select max(billNo) from '+ @sTableName +' where billNo in'+' (select billNo from '+ @sTableName +' where left(BillNo,8)= '+ @sBiLLNo + '))'
exec (@sql)Create Proc Pro_Find
@sTableName varchar(20),
@sBillNo varchar(20) output
As
exec @sBillNo = Max_billno @sTableName,@sBillNo