Eminem Eminem - 2 months ago 8
SQL Question

sql server 2014 Why do I get the error with the code in a view and not stored procedure

When I try to add the following code to a view:

SELECT [Albi-Anagrafe-Associati].AnaUnica,
[Albi-Anagrafe-Associati].Albo,
[Albi-Anagrafe-Associati].Comune,
[Albi-Anagrafe-Associati].ComunePDC,
IIf([Comune] <> [ComunePDC], 'NO', 'OK') AS Attenzione,
[Albi-Anagrafe-Associati].Paghe,
[Albi-Anagrafe-Associati].ContOrd,
[Albi-Anagrafe-Associati].ContSem
FROM [Albi-Anagrafe-Associati]
WHERE ( ( ( [Albi-Anagrafe-Associati].ComunePDC ) <> 'Extra Provincia' )
AND ( ( IIf([Comune] <> [ComunePDC], 'NO', 'OK') ) = 'NO' ) );


I get the error:


Error in list of function arguments: '<' not recognized.

Unable to parse query text.


But when I add the same code in a stored procedure I get no error.

What is the reason for the difference in behaviour?

Answer

This error comes from the visual designer. This apparently hasn't been updated to cope with the more recent syntax additions.

Don't use that. It is buugy and very limited anyway. Just open a new query window and execute

CREATE VIEW dbo.SomeName
AS
/*Paste your code here*/

and it will work fine if you are on a version later than 2012.

There are no actual syntax errors in your code though there are several things that can be improved.

Fixing the formatting, removing the unneeded two part names and simplifying the WHERE clause and SELECT list results in

SELECT AnaUnica,
       Albo,
       Comune,
       ComunePDC,
       'NO' AS Attenzione, /*Guaranteed by the WHERE that [Comune] <> [ComunePDC]*/
       Paghe,
       ContOrd,
       ContSem
FROM   dbo.[Albi-Anagrafe-Associati]
WHERE  ComunePDC NOT IN ('Extra Provincia',Comune);
Comments