CODEWITHSUNDEEP
sql-serversql-function
Alexandre
Alexandre

Reputation: 2025

Create a function to return a table SQL

I'm creating a function to return an table in SQL, but, I don't know how exactly to create the function, my function at now is:

CREATE FUNCTION fn_PegaDetalhesDoPagador (  @IdLoteDet bigint )
RETURNS TABLE 
(
    NomeSacado varchar(60),
    CNPJSacado varchar(20),
    Compromisso varchar(25)
)
AS
RETURN 
(
    SELECT  Sacados.NomeSac, 
        LoteDet.IdLoteDet,
        LoteDet.NossoNum,
        LoteDet.Comprom,
        dbo.Sacados.CNPJ
    FROM    dbo.LoteDet INNER JOIN dbo.Sacados ON dbo.LoteDet.IDSac = dbo.Sacados.IDSac
    WHERE idlotedet=@IdLoteDet

    IF @@ROWCOUNT = 0
        BEGIN
            SELECT  Sacados.NomeSac, 
            LoteDetPg.IdLoteDet,
            LoteDetPg.NossoNum,
            LoteDetPg.Comprom,
            dbo.Sacados.CNPJ
            FROM    dbo.LoteDetPg INNER JOIN dbo.Sacados ON dbo.LoteDetPg.IDSac = dbo.Sacados.IDSac
            WHERE idlotedet=@IdLoteDet
            RETURN
        END
    else
        RETURN
)

What I need, if the select returns nothing from table LoteDet, I need than the function do the select in table LoteDetPg.

It's possible to do this using this kind of function ?

It's possible to do this with some other method ?

Upvotes: 0

Views: 1020

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

Try this:

CREATE FUNCTION fn_PegaDetalhesDoPagador ( @IdLoteDet BIGINT )
RETURNS @res TABLE
(
  NomeSacado VARCHAR(60) ,
  IdLoteDet VARCHAR(MAX) ,--check this cols data type
  NossoNum VARCHAR(MAX) ,--check this cols data type
  Compromisso VARCHAR(25) ,
  CNPJSacado VARCHAR(20)
)
AS
BEGIN
    IF EXISTS ( SELECT TOP 1 Sacados.NomeSac --better if you replace this with PK col
                FROM    dbo.LoteDet
                        INNER JOIN dbo.Sacados ON dbo.LoteDet.IDSac = dbo.Sacados.IDSac
                WHERE   idlotedet = @IdLoteDet )
        BEGIN
            INSERT  INTO @res
                    SELECT  Sacados.NomeSac ,
                            LoteDet.IdLoteDet ,
                            LoteDet.NossoNum ,
                            LoteDet.Comprom ,
                            dbo.Sacados.CNPJ
                    FROM    dbo.LoteDet
                            INNER JOIN dbo.Sacados ON dbo.LoteDet.IDSac = dbo.Sacados.IDSac
                    WHERE   IdLoteDet = @IdLoteDet;
        END;
    ELSE
        BEGIN
            INSERT  INTO @res
                    SELECT  Sacados.NomeSac ,
                            LoteDetPg.IdLoteDet ,
                            LoteDetPg.NossoNum ,
                            LoteDetPg.Comprom ,
                            dbo.Sacados.CNPJ
                    FROM    dbo.LoteDetPg
                            INNER JOIN dbo.Sacados ON dbo.LoteDetPg.IDSac = dbo.Sacados.IDSac
                    WHERE   IdLoteDet = @IdLoteDet;
        END;

    RETURN;
END;

Upvotes: 2

atul kushwaha
atul kushwaha

Reputation: 1

First Select count of data from LoteDet Table and stores into a variable,if variable count is not zero,fetch data from LoteDet otherwise use LoteDetPg Table to fetch data

Upvotes: 0

Related Questions