Bouzi Bouzi - 6 months ago 8
SQL Question

Function nested in stored procedure that have parameters in SQL server below but not work

I have a function nested in stored procedure that have parameters in SQL server below but not work.

CREATE FUNCTION [dbo].[Select_ByStation1] (@Station NVARCHAR (20), @BrqDateStart DATETIME = NULL, @BrqDateEnd DATETIME = NULL)
RETURNS TABLE
AS

IF ((@BrqDateStart IS NOT NULL)AND(@BrqDateEnd IS NOT NULL))
BEGIN
RETURN
( SELECT * FROM dbo.Select_ByStation(@Station)AS TAB
WHERE TAB.BrqDate BETWEEN (@BrqDateStart)AND(DATEADD(S,-1, DATEADD(D, 1, @BrqDateEnd))));
END

ELSE IF ((@BrqDateStart IS NULL)OR(@BrqDateEnd IS NULL))
BEGIN

RETURN
( SELECT * FROM dbo.Select_ByStation(@Station)AS TAB);
END
GO


and i get this errors


Msg 156, Level 15, State 1, Procedure Select_ByStation1, Line 7
Incorrect syntax near the keyword 'IF'. Msg 178, Level 15, State 1,
Procedure Select_ByStation1, Line 9 A RETURN statement with a return
value cannot be used in this context. Msg 178, Level 15, State 1,
Procedure Select_ByStation1, Line 17 A RETURN statement with a return
value cannot be used in this context.

Answer

An in-line table valued function cannot have an if . . . just a query. You could change this to:

CREATE FUNCTION [dbo].[Select_ByStation1] (@Station NVARCHAR (20), @BrqDateStart DATETIME = NULL, @BrqDateEnd   DATETIME = NULL)
RETURNS @retval TABLE (
    <list columns here>
   )
AS
BEGIN

    IF ((@BrqDateStart IS NOT NULL)AND(@BrqDateEnd IS NOT NULL))
        BEGIN
         insert into retval (. . .)
            SELECT *
            FROM dbo.Select_ByStation(@Station)AS TAB    
            WHERE TAB.BrqDate BETWEEN (@BrqDateStart)AND(DATEADD(S,-1, DATEADD(D, 1, @BrqDateEnd))));
        END

    ELSE IF ((@BrqDateStart IS NULL)OR(@BrqDateEnd IS NULL))
        BEGIN
            INSERT INTO @retval (. . .)
                SELECT *
                FROM dbo.Select_ByStation(@Station)AS TAB;
        END 
END;

Note: This assumes that the two queries return the same table structure.

Comments