3

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?

Daan
  • 6,952
  • 4
  • 29
  • 36

3 Answers3

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
Dewfy
  • 23,277
  • 13
  • 73
  • 121
  • Thinking in the same way - create view or table-function (with 1 param) from UNION and use it as simple query. – Dewfy Aug 06 '09 at 13:06
0

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

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
0

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
Grzegorz Gierlik
  • 11,112
  • 4
  • 47
  • 55