CODEWITHSUNDEEP
sql-server
Niclas Gleesborg
Niclas Gleesborg

Reputation: 566

SQL Server If-statement Undeclared variable

I have the following code: 

CREATE FUNCTION db_owner.GetComp
(
    @CompID bigint,
    @ComponentType nvarchar(50)
)
RETURNS TABLE
AS
    RETURN /* SELECT ... FROM ... */

    IF (@ComponentType = 'WMCOMP') begin

        RETURN

        SELECT * 
        FROM WMCOMP 
        WHERE wmcompid = @CompID

    end
    ELSE IF (@ComponentType = 'ADECOMP') begin

        RETURN

        SELECT * 
        FROM ADECOMP 
        WHERE adecompid = @CompID

    end

When trying to save it in Visual Studio, the following error is displayed:

Incorrect syntax near IF

I simply cannot see what is wrong. Any help will be appreciated.

Upvotes: 0

Views: 359

Answers (2)

Devart
Devart

Reputation: 122002

Try this one -

CREATE FUNCTION db_owner.GetComp
(
      @CompID bigint
    , @ComponentType nvarchar(50)
)
RETURNS @Result TABLE (col1 INT, ...) 
AS
BEGIN

    INSERT INTO @Result (col1, ...)
    SELECT *
    FROM (
        SELECT *
        FROM dbo.WMCOMP
        WHERE wmcompid = @CompID
            AND @ComponentType = 'WMCOMP'

        UNION ALL

        SELECT *
        FROM dbo.ADECOMP
        WHERE adecompid = @CompID
            AND @ComponentType = 'ADECOMP'
    ) f

    RETURN

END

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300719

You have a missing comma in param list:

CREATE FUNCTION db_owner.GetComp
(
    @CompID bigint,    --<---  need a comma here
    @ComponentType nvarchar(50)
)

Upvotes: 2

Related Questions