SQL Server Programming - syntax error

Author

syntax error

TJS

2005-06-08, 8:59 pm

can someone explain solution to the following syntax error :

The following error occured while executing the query:
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'IF'.

========================================
====
create FUNCTION fn_test_yaks
(
@admin nvarchar(10),
@YearID int,
@ClientID int,
@ProjectID int
)
RETURNS TABLE
AS

RETURN

IF @admin = 'true' and @ClientID <> -1
BEGIN
SELECT *
FROM Invoices i INNER JOIN Projects p
ON i.IDProject = p.ProjectID
WHERE i.IDProject = @projectid
and year(issueDate) = @YearID
and IDuser = @ClientID
END

GO

Hari Prasad

2005-06-08, 8:59 pm

Hi,

Usage of function is wrong. See the below sample and work out.

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S
INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
WHERE O.Freight > @FreightParm
RETURN
END

Thanks
hari
SQL Server MVP
"TJS" <nospam@here.com> wrote in message
news:OdVUIIHbFHA.2768@tk2msftngp13.phx.gbl...
> can someone explain solution to the following syntax error :
>
> The following error occured while executing the query:
> Server: Msg 156, Level 15, State 1, Line 13
> Incorrect syntax near the keyword 'IF'.
>
> ========================================
====
> create FUNCTION fn_test_yaks
> (
> @admin nvarchar(10),
> @YearID int,
> @ClientID int,
> @ProjectID int
> )
> RETURNS TABLE
> AS
>
> RETURN
>
> IF @admin = 'true' and @ClientID <> -1
> BEGIN
> SELECT *
> FROM Invoices i INNER JOIN Projects p
> ON i.IDProject = p.ProjectID
> WHERE i.IDProject = @projectid
> and year(issueDate) = @YearID
> and IDuser = @ClientID
> END
>
> GO
>

Hugo Kornelis

2005-06-10, 8:58 pm

On Wed, 8 Jun 2005 14:35:44 -0700, TJS wrote:

>can someone explain solution to the following syntax error :
>
> The following error occured while executing the query:
>Server: Msg 156, Level 15, State 1, Line 13
>Incorrect syntax near the keyword 'IF'.

Hi TJS,

Hari already posted an explanation and an alternative, but his version
was a multi-statement table-valued user-defined function. If you prefer
to use an inline table-valued user-defined function, try:

create FUNCTION fn_test_yaks
(
@admin nvarchar(10),
@YearID int,
@ClientID int,
@ProjectID int
)
RETURNS TABLE
AS

RETURN
SELECT *
FROM Invoices i INNER JOIN Projects p
ON i.IDProject = p.ProjectID
WHERE i.IDProject = @projectid
and year(issueDate) = @YearID
and IDuser = @ClientID
and @ClientID <> -1
and @admin = 'true'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links