我不能使用“DECLARE”吗？ 在表函数中？

Question

感谢您的消息，我像这样解决了我的问题，

ALTER FUNCTION [VEZNE].[fnMakbuzIslemGetir] 
        ( 
              @refNo     as int      
        ) 

RETURNS @tablename TABLE (kontrol1 char(1),key0 numeric(18,0) ,tarih datetime ,hizkod char(12),hizad char(75),ytlhizfiyat decimal(18,2) ,hizmiktar numeric(18,2),ytlhiztutar decimal(18,2))
        AS 
        BEGIN

            DECLARE @durum     AS VARCHAR
            DECLARE @hastaTuru AS VARCHAR 
            DECLARE @makTipi   AS VARCHAR 

            SET @durum     = (SELECT durum FROM TH_RefKart WHERE RefNo = @refNo)
            SET @hastaturu = (SELECT HastaTuru FROM TH_Dosya WHERE DosyaNo in (SELECT DosyaNo FROM TH_RefKart WHERE RefNo = @refNo))
            SET @makTipi   = (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo )

            IF ((@hastaTuru = 'Ücretli') AND (@durum = 'AYAKTA')) OR @makTipi = '0' 
            BEGIN 
                INSERT @tablename SELECT kontrol1, key0, tarih, hizkod, hizad, ytlhizfiyat, hizmiktar, ytlhiztutar 
                FROM TH_Islem 
                WHERE refno= @refNo and DekontNo = '0' and anahtar<> -1 
            END 

        RETURN

        END

但现在我在这一行遇到了另一个问题，

SET @makTipi   = (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo)

因为此 SELECT 返回多行值。 我现在能做什么？

Answer 1

好的，所以 (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) 返回多个值，现在做什么取决于你想做什么。 因此，如果您想要这些结果中的最大值，请使用：

(SELECT MAX(maktipi) FROM TH_Islem WHERE refNo = @refNo )

或 MIN：

(SELECT MIN(maktipi) FROM TH_Islem WHERE refNo = @refNo )

或者稍后使用 @makTipi 来检查某些内容，也许您确实希望保留所有返回的值。 在这种情况下，您需要将 @makTipi 更改为表，并将从 (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) 返回的所有值插入其中。 例如：

DECLARE @makTipi TABLE(columName AS VARCHAR)
INSERT @makTipi SELECT maktipi FROM TH_Islem WHERE refNo = @refNo

然后稍后您

IF ((@hastaTuru = 'Ücretli') AND (@durum = 'AYAKTA')) OR @makTipi = '0'

将其更改为：

IF ((@hastaTuru = 'Ücretli') AND (@durum = 'AYAKTA')) OR EXISTS (SELECT * FROM @makTipi WHERE columnName = '0' )

希望这有帮助！

Answer 2

试试这个：

CREATE FUNCTION [dbo].[fnMakbuzIslem] 
( 
      @refNo     as int      
) 

RETURNS @tablename TABLE (kontrol1 INT,key0 INT ,tarih INT ,hizkod INT,hizad INT,ytlhizfiyat INT,hizmiktar INT,ytlhiztutar INT)
AS 
BEGIN

DECLARE @durum     AS VARCHAR = (SELECT durum FROM TH_RefKart WHERE RefNo = @refNo) 
DECLARE @hastaTuru AS VARCHAR = (SELECT HastaTuru FROM TH_Dosya WHERE DosyaNo in (SELECT DosyaNo FROM TH_RefKart WHERE RefNo = @refNo)) 
DECLARE @makTipi   AS VARCHAR = (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo) 

 IF ((@hastaTuru = 'Ücretli') AND (@durum <> 'AYAKTA')) OR @makTipi = '0' 
 BEGIN 
       INSERT @tablename SELECT kontrol1,key0,tarih,hizkod,hizad,ytlhizfiyat,hizmiktar,ytlhiztutar 
       FROM TH_Islem 
       WHERE refno= @refNo and DekontNo = '0' and anahtar<> -1 
 END 

RETURN

END
GO

如果所有列都假定为 INT，请进行更改以满足您的需要。