0

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.

Enzo Quiroz
  • 3
  • 2
  • Please tag your question with the database you are using. As a note, this works fine in SQL Server (https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=302d1449eb171f12acd9380485f9b53f). – Gordon Linoff Jun 11 '19 at 14:03
  • Actually the problem occurs in SQL Server, in one database it executes, in the other it shows the error – Enzo Quiroz Jun 11 '19 at 14:20
  • Check the database compatibility level of both databases, and try explicitly prefixing the invocation with a schema name (`dbo.TEST`). I seem to recall older versions were more picky about this. – Jeroen Mostert Jun 11 '19 at 14:22
  • Thank you. It was the compatibility level in the databases that doesn't work it was 80 and in the other database it was 100. I changed that and now works perfect !! – Enzo Quiroz Jun 11 '19 at 14:40

1 Answers1

0

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);
Luis Cazares
  • 3,495
  • 8
  • 22