Can't I use "DECLARE" in table function?

Question

Thanks for your message I solved my problem like this

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

but now I have another problem at this row

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

because this SELECT returns multi rows value. what can I do now?

Answer 1

Ok, so (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) returns multiple values, what to do now depends on what you want to do. So if you want the maximum value from those results use:

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

Or MIN:

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

Or as @makTipi is used later on to check for something, maybe you do want to retain all the values returned. In that case you'd need to change the @makTipi to a table and insert all the values returned from (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) into it. Such as:

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

Then later on where you have:

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

Change this to:

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

Hope this helps!