Using IF in SQL Function with 2 Table

Question

I have a page like this

Ödeme

Baş Tar. --> Start Date Bit. Tar --> End Date

I have a SQL like this for this page,

SELECT  B.HESAP_NO, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2 AS 'TEKLIF',B.MUS_K_ISIM,
dbo.fngcodeme(B.HESAP_NO, B.DOVIZ_KOD, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2,'01/01/2011', '11/03/2011') AS 'YATAN',
(CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END) AS 'KUR',
D.AVUKAT, 
(CASE WHEN D.HESAP IN (SELECT T_HESAP_NO FROM TAKIP) THEN 
(SELECT CONVERT(VARCHAR(10),ICRA_TAR,103) FROM TAKIP WHERE T_HESAP_NO = D.HESAP)
ELSE ' ' END) AS 'ICRA TARİHİ', 
(CASE WHEN D.HESAP IN (SELECT T_HESAP_NO FROM TAKIP) THEN 
(SELECT CONVERT(VARCHAR(10),HACIZ_TAR,103) FROM TAKIP WHERE T_HESAP_NO = D.HESAP)
ELSE '' END) AS 'HACİZ TARİHİ'
FROM YAZ..MARDATA.S_TEKLIF B, AVUKAT D
WHERE B.HESAP_NO = D.HESAP
AND B.KAPANIS_TAR IS NULL
AND ISNULL(dbo.fngcodeme(B.HESAP_NO, B.DOVIZ_KOD, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2,'01/01/2011', '11/03/2011'),0) > 0
GROUP BY B.HESAP_NO, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2,B.DOVIZ_KOD ,B.MUS_K_ISIM, D.AVUKAT, D.HESAP

And this is my dbo.fngcodeme Function,

ALTER FUNCTION [dbo].[fngcodeme]
(
    @HESAP INT, @DOV INT, @TEKLIF VARCHAR(10), @BAS  VARCHAR(10), @BIT  VARCHAR(10)
)
RETURNS FLOAT

AS
BEGIN

DECLARE @Result FLOAT

SET @Result = (SELECT SUM(TUTAR)
    FROM YAZ..MARDATA.M_GHAREKET
    WHERE TEMEL_HESAP = @HESAP
    AND DOVIZ_KOD = @DOV
    AND REF_KOD = 'GC'
    AND BACAK_GRUP = 'PERT'
    AND ISL_KOD = 1
    AND ACIKLAMA LIKE '%' + @TEKLIF + '%'
    AND ISL_TAR >= CONVERT(DATETIME, + '' + @BAS + '',103)
    AND ISL_TAR <= CONVERT(DATETIME, + '' + @BIT + '',103)
    )

RETURN @Result

END

What i want is

IF `Bit. Tar. (End Date)` is less or equal then 28/02/2011 --> Use M_HAREKET(table)
IF  Baş. Tar. (Start Date) is great or equal then 01/03/2011 --> Use M_GHAREKET(table)

How can i do that?

Should i change SQL or Function? Which one?

You have two conditions that are not complimentary. What if end date is > 28/2 and start date is < 28/2 ? Your 2 cases does not cover it — RichardTheKiwi
– RichardTheKiwi, Commented Mar 16, 2011 at 22:20
@Richard this don't matter for this page. The "User" will enter only ..... - 28/02/2011 or 01/03/2011 - ..... date range. — Soner Gönül
– Soner Gönül, Commented Mar 17, 2011 at 6:09

RichardTheKiwi · Accepted Answer · 2011-03-17 07:04:31Z

2

If @BAS and @BIT are dates, pass them in as dates. Don't use VARCHAR.

ALTER FUNCTION [dbo].[fngcodeme]
(
    @HESAP INT, @DOV INT, @TEKLIF VARCHAR(10), @BAS datetime, @BIT datetime
)
RETURNS FLOAT

AS
BEGIN

DECLARE @Result FLOAT

IF CONVERT(DATETIME, @BIT,103) <= '20110228'
    SET @Result = (SELECT SUM(TUTAR)
        FROM YAZ..MARDATA.M_HAREKET
        WHERE TEMEL_HESAP = @HESAP
        AND DOVIZ_KOD = @DOV
        AND REF_KOD = 'GC'
        AND BACAK_GRUP = 'PERT'
        AND ISL_KOD = 1
        AND ACIKLAMA LIKE '%' + @TEKLIF + '%'
        AND ISL_TAR >= CONVERT(DATETIME, @BAS,103)
        AND ISL_TAR <= CONVERT(DATETIME, @BIT,103)
        )
ELSE
    SET @Result = (SELECT SUM(TUTAR)
        FROM YAZ..MARDATA.M_GHAREKET
        WHERE TEMEL_HESAP = @HESAP
        AND DOVIZ_KOD = @DOV
        AND REF_KOD = 'GC'
        AND BACAK_GRUP = 'PERT'
        AND ISL_KOD = 1
        AND ACIKLAMA LIKE '%' + @TEKLIF + '%'
        AND ISL_TAR >= CONVERT(DATETIME, @BAS,103)
        AND ISL_TAR <= CONVERT(DATETIME, @BIT,103)
        )

RETURN @Result

END

As for the calling function, don't use region specific date formats. Stick to YYYYMMDD or one of the ISO/XML ones. So your query would be

SELECT  B.HESAP_NO, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2 AS 'TEKLIF',B.MUS_K_ISIM,
dbo.fngcodeme(B.HESAP_NO, B.DOVIZ_KOD, B.TEKLIF_NO1 + '/' + B.TEKLIF_NO2,'20110101', '20110311') AS 'YATAN',
......

edited Mar 17, 2011 at 7:04

answered Mar 17, 2011 at 6:36

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Soner Gönül Over a year ago

Thanks! But in my sql i having an error like this "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Soner Gönül Over a year ago

In my aspx page i have this SQL heypasteit.com/clip/UWL but I'm getting again this error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. "

RichardTheKiwi Over a year ago

Why are you doing this - CONVERT(VARCHAR(10),@S_TARIH_B,103)? is @S_TARIH_B a date value or a varchar? If it is varchar, you want to convert(datetime not convert to varchar again

Collectives™ on Stack Overflow

Using IF in SQL Function with 2 Table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related