Table valued parameter problem involving operation

Question

I am creating a view which uses a table valued function.

Here is a simple function:

CREATE FUNCTION TEST(@COD INT)
RETURNS TABLE
AS
RETURN SELECT @COD COD

When I execute:

SELECT * FROM DBO.TEST(1)

it runs perfect, but when I make an operation inside the functions it shows me an error:

SELECT * FROM DBO.TEST(1+1)

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.

also when I use parenthesis there is an error:

SELECT * FROM DBO.TEST((1+1))

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

I have another database, when I execute all the examples above it runs perfect. I think it could be a server configuration or database configuration but I don't know what I am looking for.

Answer 1

This might be a problem that was fixed at some point in a SQL Server release.

As an alternative, you can use APPLY to correct this problem.

SELECT COD
FROM (SELECT 1+1 AS n)x
CROSS APPLY dbo.TEST(x.n);