CODEWITHSUNDEEP
sqlsql-server-mobile
Daan
Daan

Reputation: 6994

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

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?

Upvotes: 3

Views: 2931

Answers (3)

Dewfy
Dewfy

Reputation: 23624

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

Upvotes: 1

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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

Upvotes: 0

Guffa
Guffa

Reputation: 700372

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

Upvotes: 0

Related Questions