日期:2014-05-18  浏览次数:20501 次

SQL如何在存储过程里写一个代码显示两个表的字段?
如题,举例,两张表1:Users,2:data
1:USERS中有字段 user_code,user_name
2:data中有字段user_code,steam...
现在要写一个存储过程,我这样写的。
SQL code

creat procedure query
CREATE PROCEDURE [dbo].[query]
(
         @User_name   varchar(50),
     --  @Check_time  datetime=null,
       @User_code    varchar(50)
)

 AS
select data.User_code  --as @user_name,data.steam
where data.User_code=User.User_code and data.User_code=@User_code 
GO



我想在查询分析器中查询的时候,user_code 字段显示 users中的对应USE_code的名字
例如:有个users中有1001,大学
  data中有1001,100
查询显示
query 1001
显示如下
大学 steam
1001 100



谢谢各位了

------解决方案--------------------
select 大学,stream
from tb1,tb2
where tb1.user_code=tb2.user_code
------解决方案--------------------
SQL code
select 
 b.user_code,a.user_name 
from
 USERS a,data b
where
 a.user_code=b.user_code

------解决方案--------------------
SQL code
select data.User_code ,data.steam
from data,User
where data.User_code=User.User_code and data.User_code=@User_code

------解决方案--------------------
SQL code
--创建测试临时表
create table #users(user_code varchar(5),user_name varchar(20))
create table #data(user_code varchar(5),steam int)


--插入测试数据
insert into #users
select '1001','大学' union all
select '1002','小学' union all
select '1003','中学' 


insert into #data
select '1001',100 union all
select '1003',100 union all
select '1002',100 union all
select '1001',80

go

--创建查询用 测试存储过程
create procedure test
    @user_code varchar(5)
as
begin
    set nocount on 
    declare @exec varchar(200)
    declare @col_name varchar(20)
    set @col_name = '其他'
    select @col_name = user_name from #users where user_code = @user_code
    
    set @exec = 'select user_code as [' + @col_name + '] ,steam  from #data where user_code = ''' + @user_code + ''''
    exec (@exec)
end
go

--执行存储过程
exec test '1002'
go

--清理
drop procedure test
drop table #users
drop table #data
go

------解决方案--------------------
SQL code
create table #users(user_code varchar(5),user_name varchar(20))
create table #data(user_code varchar(5),steam int)


--插入测试数据
insert into #users
select '1001','大学' union all
select '1002','小学' union all
select '1003','中学' 


insert into #data
select '1001',100 union all
select '1003',100 union all
select '1002',100 union all
select '1001',80

go
create proc pr_test
@user_code varchar(100)
as
begin 
    declare @s varchar(1000)
    select @s='select user_code as ' + QUOTENAME(user_name) +
         ' ,steam  from #data where user_code = '''+@user_code+''''
    from #users
    where user_code = @user_code
    exec(@s)
end
go
exec pr_test '1002'


drop proc pr_test
drop table #users
drop table #data
go

/*
小学    steam
----- -----------
1002  100

(1 行受影响)

*/