Duplicated parameter names are not allowed.

I am getting this exception in RC1:

SqlCException: Duplicated parameter names are not allowed. [ Parameter name = @.SOLUTIONID ]

With the following statement:

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID IN (SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID);

Besides the obvious of adding a new parameter with the same value and a different name - are there any plans to fix this?

Another area where SQLCE seems deficient is non-support of SCOPE_IDENTITY(), which although not important for CE itself is very important for TSQL compatibility with SQL Server - where obviously @.@.IDENTITY won't cut it.

A related problem to this is that SQL CE doesn't support batch statements - which sadly destroys the useful pattern of inserting a record with an INSERT and doing an identity SELECT in the same batch.

When programming against a SQL Server there is benefit from doing as much work as possible in one round trip. However with CE all the work is being done on your desktop so there is little to be gained in creating complex, multistepped batches.

Have you considered splitting your query into 2? Secondly have you considered dynamically creating the SQL, or a prepared query?

By splitting it as:

SELECT MAX(VERSIONID)
FROM VERSIONS WHERE SOLUTIONID = @.SOLUTIONID

as a scalar query and then executing the second part as

SELECT
[VERSIONID],
[SOLUTIONID],
[BASEVERSIONID],
[VERSIONNUMBER],
[NAME],
[DESCRIPTION],
[CREATEDATE],
[UPDATEDATE],
[VERSIONCLOSED]
FROM VERSIONS
WHERE SOLUTIONID = @.SOLUTIONID AND
VERSIONCLOSED = 1 AND
VERSIONID = @.MAXVERSIONID

it allows you to use an index on VERSIONID.

In addition you can check that there is a MAX(VERSIONID) and if there isn't then insert some program logic at this step to avoid the second SELECT.

As for the @.@.IDENTITY problem couldn't you use the Insert method of SQLCEResultSet and then read the identity column?

Just a thought
|||Thanks for the indexing tips Brian, much appreciated - the example TSQL given is unoptimized it's true.

However my question wasn't really about indexing - it was about the subtle differences between SQL Server and SQL CE TSQL (mostly syntactical) which make it really hard to share TSQL between SQL Server and SQL CE.

Is there any chance of SCOPE_IDENTITY() and support for batches sneaking into a version sometime soon? We are using the SQLClient and SQLCE providers via the really useful provider factory in ADO.NET so if the TSQL could be shared that would be ideal.|||I suspect that in the short term SQL CE version of T-SQL will remain very close to that of SQL Mobile from which it has descended.

Perhaps in CE 2.0 some of the features will be added? but wont the mobile people then complain?