Can I use an ODBC parameter twice?

Question

I have the following ODBC query from SSRS to a MySQL database:

SELECT     ID,  StartTime, StartTimeMS, EndTime, EndTimeMS, TIMEDIFF(EndTime, StartTime) AS CallDuration, CallType, CallerID, DialedNumber, Extension
FROM        `call`
WHERE     (CallerID = ?) OR
                  (Extension = ?) AND (StartTime < ?) AND (StartTime > ?)

When I run SSRS, I get prompted twice for the first two parameters. Ideally, I'd just have to enter that value once and be able to use it twice. Can I use a positional parameter inside an SSRS query twice so the user isn't prompted twice?

Answer 1

I don't think you can pass named parameters to MySQL like that but I think you can create them in the query:

SET @number = ?
SET @EndDate = ?
SET @BeginDate = ?


SELECT     ID,  StartTime, StartTimeMS, EndTime, EndTimeMS, TIMEDIFF(EndTime, StartTime) AS CallDuration, CallType, CallerID, DialedNumber, Extension
FROM        `call`
WHERE    ( (CallerID = @number) OR (Extension = @number) ) AND (StartTime < @EndDate) AND (StartTime > @BeginDate)

Incidently, I think you need to put your OR clauses in a separate set of parenthesis.