函数在执行时返回错误的值。

I have a stored procedure like this:

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

ALTER procedure [dbo].[fetchkey]
@carid nvarchar(50) =null
as
begin
 select t.TBarcode, t.Status, [dbo].[keyloc](t.Status) as 'Key location'
from Transaction_tbl t 
 where t.TBarcode=@carid
end

I have one function associated with this stored procedure:

我有一个与这个存储过程相关的函数:

ALTER function [dbo].[keyloc](@status numeric(18,2)) RETURNS varchar(50)
as  begin  declare 
@car nvarchar(100), @keylocation  Varchar(50)
 if @status=0
begin
select @car =  t1.TBarcode from Transaction_tbl t1 
  select @keylocation='With Employee'+'('+@car+')'
return @keylocation
end

I am passing carid as my Tbarcode, but while executing out put is coming wrong

我正在传递carid作为我的Tbarcode，但是执行的时候出了错误。

my output:

    TBarcode             Status      Key location
    -------------------- ----------- ----------------------
    53012364813          0           With Employee(717164016319).

I want to get same Tbarcode inside the With Employee

我想在员工内部得到相同的Tbarcode。

Expected output

    TBarcode             Status      Key location
    -------------------- ----------- ----------------------
    53012364813          0           With Employee(53012364813).

What is wrong with my code?

我的代码有什么问题?

1 个解决方案

#1

You are returning

你返回

select @car =  t1.TBarcode from Transaction_tbl t1

this from your function. Since it does not have proper where clause @car is assigned by the first value of TBarcode .

这从你的函数。因为它没有合适的where子句@car是由TBarcode的第一个值分配的。

Hence the function returns the first value of TBarcode from Transaction_tbl as output

因此函数返回Transaction_tbl作为输出的TBarcode的第一个值。

Try this

试试这个

ALTER function [dbo].[keyloc](@status numeric(18,2),@cardID VARCHAR(50)/*Newly added*/)
 RETURNS varchar(50)
ASbegin  declare 
  @car nvarchar(100), @keylocation  Varchar(50)
   if @status=0
  begin
    select @car =  t1.TBarcode 
    from Transaction_tbl t1 
    WHERE t1.TBarcode =@cardID 
    select @keylocation='With Employee'+'('+@car+')'

return @keylocation
end

Procedure

过程

ALTER procedure [dbo].[fetchkey]
@carid nvarchar(50) =null
as
begin
 select t.TBarcode, t.Status,
 [dbo].[keyloc](t.Status,@carid/*newly added*/) as 'Key location'
from Transaction_tbl t 
 where t.TBarcode=@carid
end

#1