Create a function to return a table SQL

Question

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 ?

If you use SSMS (any version) then expand Programmability \ Function then right click and select "New Multistatement..." and use the template provided. — Alex Kudryashev
– Alex Kudryashev, Commented Jul 1, 2016 at 20:00

Vahid Farahmandian · Accepted Answer · 2016-07-02 11:55:19Z

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;

atul kushwaha · Accepted Answer · 2016-07-02 06:25:21Z

0

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

answered Jul 2, 2016 at 6:25

atul kushwaha

11 bronze badge

Collectives™ on Stack Overflow

Create a function to return a table SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related