Reputation:

SQL statement problem

I have this code:

SELECT    idcallhistory3, callid, starttime, answertime, endtime, duration,
          is_answ, is_fail, is_compl, is_fromoutside, mediatype, from_no,
          to_no, callerid, dialednumber, lastcallerid, lastdialednumber,
          group_no, line_no
FROM      "public".callhistory3
WHERE     (starttime >= ?) AND (endtime <= ?) AND (is_fromoutside = ?) 
          AND (from_no = ?) AND (to_no = ?)

The problem is I need to pass one value for ? and get all the result without filter, some thing like *

Any help?

Upvotes: 2

Answers (6)

Mess

Reputation:

I agree with Jamal Hansen. COALESCE is by far the best performing way to go, at least on SQL Server

Upvotes: 0

Jamal Hansen

Reputation: 954

I like COALESCE.

You just have to be careful with null values on the left hand side, if there can be nulls on the left hand side you can do something like the last line so that nulls will match. Typically with anything like this though you will want to make sure your query still performs ok.

SELECT    idcallhistory3, callid, starttime, answertime, endtime, duration,
          is_answ, is_fail, is_compl, is_fromoutside, mediatype, from_no,
          to_no, callerid, dialednumber, lastcallerid, lastdialednumber,
          group_no, line_no
FROM      "public".callhistory3
WHERE     (starttime >= COALESCE(@starttime, starttime )) 
          AND (endtime <= COALESCE(@endtime, endtime)) 
          AND (is_fromoutside = COALESCE(@is_fromoutside, is_fromoutside)) 
          AND (from_no = COALESCE(@from_no, from_no)) 
          AND (COALESCE(to_no, -1) = COALESCE(@to_no, to_no, -1)) -- make nulls match

Upvotes: 3

Marc Gravell

Reputation: 1064104

For complex queries with multiple optional sections, you may find that it is better to create the SQL to suit. You can do this either at the caller (for example, in C#), or in the database (at least, with SQL Server) - but either way, you must ensure it remains parameterised. With the caller doing the work, it is just a case of adding suitable parameters to the command. If the db is generating the TSQL, then the approach depends on the RDBMS. With SQL-Server, sp_ExecuteSql is your friend - i.e. you might build a @cmd variable based on the query, then:

EXEC sp_ExecuteSQL @cmd, N'@someArg int', @actualArg

Where @someArg is the declaration inside @cmd, and @actualArg is the value to pass in at execution time.

Upvotes: 2

user1228

Reputation:

WHERE 
  (@start is null OR starttime >= @start) AND 
  (@end is null OR endtime <= @end) AND 
  (@fromOutside is null OR is_fromoutside = @fromOutside) AND 
  (@fromNo is null OR from_no = @fromNo) AND 
  (@toNo is null OR to_no = @toNo)

Pass nulls for all parameters (dang sql nulls; thanks GC).

Upvotes: 7