将一个存储过程值用于另一个存储过程的结果。

I have a stored procedure like this:

我有一个这样的存储过程:

alter procedure [dbo].[fetchkey]
@locid int =null
as
begin
 select t.TBarcode,t.Status
from Transaction_tbl t 
left join EmployeeMaster_tbl e on t.Ecode=e.Ecode where Locid=5
end

my output

我的输出

TBarcode             Status
-------------------- -----------
57173621345          0
57173865238          1
57181456325          2
57182756600          3
58125323124          4
-----------------------------------

i have another store procedure to fetching keylocation based on the status:

我有另一个基于状态获取关键位置的存储过程:

ALTER procedure [dbo].[Keylocation]
@Carid nvarchar(50)
as
begin
SET NOCOUNT ON;
SELECT 
        t.Status,
        k.HBarcode,
        te.UniqueName,
     COALESCE(e.Ename, e1.Ename) AS EName
FROM    Transaction_tbl t
        left JOIN UserMaster_tbl u
            ON u.uid = t.PAICID
             left join EmployeeMaster_tbl e on e.ECode=u.Ecode
            AND t.Status = 0
        LEFT JOIN EmployeeMaster_tbl e1
            ON e1.ECode = t.ECode
            AND t.Status = 1 or e1.Ecode=t.DelEcode and t.Status=4
            left Join KHanger_tbl k
            on t.transactID=k.transactid
            and t.Status in(2,3)
            left JOIN Terminals_tbl te ON k.tid = te.tid

WHERE   t.TBarcode = @Carid
end

My out put like this:

我的输出是这样的:

Status      HBarcode         UniqueName                EName                         
3           001              Key Room-1                NULL

this is the result of status 3.in my first stored procedure i want to get one more column based on this result.my expected out put:

这是状态3的结果。在我的第一个存储过程中，我希望基于这个结果再得到一个列。我的预期将:

TBarcode             Status    key location
-------------------- -----------
57173621345          0         with Employee(Ename Value)
57173865238          1         with Paic(Ename value)
57181456325          2         UniqueName value( HBarcode value)                       
57182756600          3          UniqueName value ( HBarcode value) 
58125323124          4          with driver(Ename Valu)

is there any way to do this? am new in stored procedure.is there any way to create function and call this result to my stored procedure,,if any one know please help me

有什么办法吗?是新的存储过程。是否有任何方法可以创建函数并将结果调用到我的存储过程中，如果有人知道请帮助我?

1 个解决方案

#1

Take a look at Table valued functions, this can be used to serve out output of one function as input to another store procedure. This is one of the simplest links explaining it, see here Table Valued Functions

看看表值函数，这可以用来输出一个函数的输出作为另一个存储过程的输入。这是解释它的最简单的链接之一，见表值函数。

Please see below for your case. I am not sure on what the Join should be made but if you look and understand how it is written you will surely get the idea.

请看下面的情况。我不确定这个连接应该做什么，但是如果你看并理解它是怎么写的，你肯定会明白。

CREATE FUNCTION FetchKEY(@locid int)    
 RETURNS @keydetails TABLE  (TbarCode int NOT NULL,    Status int NOT NULL )  AS 
  BEGIN   
     INSERT INTO @person (TbarCode,Status)  select t.TBarcode,t.Status from
     Transaction_tbl t  left join EmployeeMaster_tbl e on t.Ecode=e.Ecode
     where Locid=@locid     
  RETURN; 
  END;
Go

So your Store procedure would be like this

所以你的存储过程是这样的。

 ALTER procedure [dbo].[Keylocation] @Carid nvarchar(50) as begin 
 SET NOCOUNT ON; 
 SELECT 
          t.Status,k.HBarcode,
          te.UniqueName,
       COALESCE(e.Ename, e1.Ename) AS EName FROM    Transaction_tbl t
          left JOIN UserMaster_tbl u
              ON u.uid = t.PAICID
               left join EmployeeMaster_tbl e on e.ECode=u.Ecode
              AND t.Status = 0
          LEFT JOIN EmployeeMaster_tbl e1
              ON e1.ECode = t.ECode
              AND t.Status = 1 or e1.Ecode=t.DelEcode and t.Status=4
              left Join KHanger_tbl k
              on t.transactID=k.transactid
              and t.Status in(2,3)
              left JOIN Terminals_tbl te ON k.tid = te.tid  
         **Inner Join FetchKEY F on F.TbarCode = k.HBarcode**      
  WHERE   t.TBarcode = @Carid 
end

#1