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

Try this:

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

WHERE all the columns are assumed to be INT's, change to suit your needs.

Answer 2

I copy and pasted the new script you've posted, and got no errors when I executed it. So not sure why you're getting these.

Answer 3

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!