sql - problems with declare

Question

I have seriously no idea what is wrong here..

USE [PRR_NEW]
DROP FUNCTION [dbo].[fA_20]
GO
CREATE FUNCTION [dbo].[fA_20]
(   
    @Id bigint = 10
)
RETURNS TABLE 
AS
RETURN 
(
declare @m table
(
    VatIdGA nvarchar(16),
    VatIdCAT nvarchar(16)
);
insert into @m (VatIdGA, VatIdCAT) values( 9,   8)
insert into @m (VatIdGA, VatIdCAT) values(11,   3)
insert into @m (VatIdGA, VatIdCAT) values(10,   5)
insert into @m (VatIdGA, VatIdCAT) values( 5,   9)

select vr.*, m.VatIdGA
    from VatRate as vr
    left outer join @m as m on m.VatIdCAT = vr.Id

The problem is declare... Any ideas or solutions? Thanks!

Tag the dbms you're using. That code is product specific. – jarlh May 10 '17 at 10:00 — jarlh, May 10 '17 at 10:00

score 1 · Accepted Answer · answered May 10 '17 at 10:03

Judging from the syntax, this looks like SQL Server to me.
If so, this should work:

CREATE FUNCTION [dbo].[fA_20]
(   
    @Id bigint = 10
)
RETURNS TABLE 
AS

RETURN 
(
    with m  as
    (
        select VatIdGA, VatIdCAT
        FROM (values(9,   8), (11,   3), (10,   5), ( 5,   9)) v(VatIdGA, VatIdCAT)
    )
    select vr.*, m.VatIdGA
    from VatRate as vr
    left outer join m on m.VatIdCAT = vr.Id
)

sql - problems with declare

1 Answers1