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

Question

I want to run a query similar to this one on a 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, running this results in the error message:

Duplicated parameter names are not allowed. [ Parameter name = @Param ]

A workaround is of course to define @Param1 and @Param2 and assign them the same value, but this feels a bit dirty to me. Is there a cleaner workaround for this problem?

Answer 1

Add the parameter only once to the parameter collection. You can use it how may times you like in the query.

Answer 2

I've never used SQL CE, but maybe this will work:

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

Answer 3

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