How to use the same parameter more than once in a request

Question

I want to run a query similar to this in the SqlCE database:

SELECT t.Field1, t.Field2
FROM MyTable t
WHERE t.Field1 = @Param
UNION ALL
SELECT t2.Field1, t2.Field2
FROM MyOtherTable t2
WHERE t2.Field1 = @Param

However, executing this result results in an error message:

Duplicate parameter names are not allowed. [Parameter Name = @Param]

The workaround is, of course, defining @Param1and @Param2assigning them the same value, but for me it is a little dirty. Is there a more difficult solution to this problem?

Answer 1

SELECT * FROM (
SELECT t.Field1, t.Field2
FROM MyTable t
UNION ALL
SELECT t2.Field1, t2.Field2
FROM MyOtherTable t2
) sub
WHERE sub.Field1 = @Param

Answer 2

Add a parameter only once to the parameter collection. You can use it as you like in the request.

Answer 3

SQL CE, , , :

DECLARE @P int

SET @P = @Param

SELECT t.Field1, t.Field2
FROM MyTable t
WHERE t.Field1 = @P
UNION ALL
SELECT t2.Field1, t2.Field2
FROM MyOtherTable t2
WHERE t2.Field1 = @P