SQL怎么在存储过程里写一个代码显示两个表的字段

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

SQL code

creat procedure queryCREATE PROCEDURE [dbo].[query](         @User_name   varchar(50),     --  @Check_time  datetime=null,       @User_code    varchar(50)) ASselect data.User_code  --as @user_name,data.steamwhere 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 bwhere a.user_code=b.user_code
[解决办法]
SQL code
select data.User_code ,data.steamfrom data,Userwhere 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 #usersselect '1001','大学' union allselect '1002','小学' union allselect '1003','中学' insert into #dataselect '1001',100 union allselect '1003',100 union allselect '1002',100 union allselect '1001',80go--创建查询用 测试存储过程create procedure test    @user_code varchar(5)asbegin    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)endgo--执行存储过程exec test '1002'go--清理drop procedure testdrop table #usersdrop table #datago
[解决办法]
SQL code
create table #users(user_code varchar(5),user_name varchar(20))create table #data(user_code varchar(5),steam int)--插入测试数据insert into #usersselect '1001','大学' union allselect '1002','小学' union allselect '1003','中学' insert into #dataselect '1001',100 union allselect '1003',100 union allselect '1002',100 union allselect '1001',80gocreate proc pr_test@user_code varchar(100)asbegin     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)endgoexec pr_test '1002'drop proc pr_testdrop table #usersdrop table #datago/*小学    steam----- -----------1002  100(1 行受影响)*/ 

 

查看更多 下一篇