Why my simple T-Sql Function can not run in Query WIndow?

Question

I created a function in SQL Server 2012 Express with the code below.

CREATE FUNCTION IncAge(@Age AS INT)
RETURNS INT
AS
BEGIN
    DECLARE @VAR AS INT;
    SET @VAR = @Age + 10;
    RETURN @VAR;
END

And when I try to call this function from query window, I get an error.

With SELECT IncAge(20) I get error

'IncAge' is not a recognized built-in function name.

With IncAge(20) I get error

Incorrect syntax near '20'.

So what is the problem??

Community · Accepted Answer · 2020-07-29 18:27:52Z

1

Function have to always be referenced with their schema:

Try

SELECT dbo.IncAge(20)

It's a generally good idea to always use the schema qualifier (usually dbo. - see Bad Habits to Kick: avoiding the schema prefix) for everything - tables, views, stored procedures - but in the case of functions, they're mandatory

edited Jul 29, 2020 at 18:27

CommunityBot

11 silver badge

answered Jan 31, 2015 at 7:34

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

Just for completeness they don't always need to be DECLARE @Result INT; EXEC @Result = IncAge 20;SELECT @Result works fine.

Collectives™ on Stack Overflow

Why my simple T-Sql Function can not run in Query WIndow?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related