--测试:
create table t1([user] nvarchar(10),id varchar(10),paw int)
insert t1 select '物理系','200',666
union all select '电子实验室','20001', 666
union all select '光学实验室','20002',666
union all select '化学系','300',666
union all select '无机实验室','30001',666
create table t2(device nvarchar(10),id varchar(10))
insert t2 values('二极管','20001')
insert t2 values('透镜','20002')
go
--用存储过程
create proc p_qry
@user nvarchar(10)
as
declare @id varchar(11)
select @id=id+'%' from t1 where [user]=@user
select * from t2 a join t2 b on a.id=b.id
where b.id like @id
go
--调用
exec p_qry '物理系'
go
--删除测试环境
drop table t1,t2
drop proc p_qry
/*--测试结果
device id device id
---------- ---------- ---------- ----------
二极管 20001 二极管 20001
透镜 20002 透镜 20002
--测试:
create table t1([user] nvarchar(10),id varchar(10),paw int)
insert t1 select '物理系','200',666
union all select '电子实验室','20001', 666
union all select '光学实验室','20002',666
union all select '化学系','300',666
union all select '无机实验室','30001',666
create table t2(device nvarchar(10),id varchar(10))
insert t2 values('二极管','20001')
insert t2 values('透镜','20002')
go
--用存储过程
create proc p_qry
@user nvarchar(10)
as
declare @id varchar(11)
select @id=id+'%' from t1 where [user]=@user
select * from t2 a join t2 b on a.id=b.id
where b.id like @id
go
--调用
exec p_qry '物理系'
go
--删除测试环境
drop table t1,t2
drop proc p_qry
/*--测试结果
device id device id
---------- ---------- ---------- ----------
二极管 20001 二极管 20001
透镜 20002 透镜 20002