Parameter to return either one record or all

• cidr

  Ten Centuries

  Points: 1353

  More actions

  June 17, 2010 at 4:44 am

  #236607

  Hi Folks, hope everyone's well

  I've been working on a query that is fairly simple as all the transorming has been done and has been put into several reporting tables. I'm using UNION ALL in each to get all my records. This report will show project information by a utility management company. There are thousands of projects.

  Anyhow, the main reason I'm asking for help is to do with a parameter I'm using in the WHERE statement. Users are allowed to select either one project i.e. A0000123, or select all projects within a division.

  If I use

  DECLARE @ProjectCode varchar(10)

  SET @ProjectCode = 'A0000123'

  WHERE ProjectCode = @ProjectCode

  The query works quite quickly, however, because I want the option to return all, I've used

  WHERE (@ProjectCode= '-1' OR ProjectCode = @ProjectCode)

  Basically saying if it's '-1' then cancel out the parameter and return everything.

  This takes a long time as SQL server's doing a table scan. This is a real problem. What makes it worse is that it's a string. I've decided to create an integer for each project and use that.

  I was wondering if anyone had any knowledge of other ways to return all or one record. Or any advice on performance?

  Thanks for any help in advance

  P

• checkai

  Hall of Fame

  Points: 3041

  More actions

  June 17, 2010 at 6:45 am

  #1181515

  I have done things like this...probably not the best....also i'd suggest indexing that INT you've created...

  IF @projectID = 0

  set @projectID = NULL

  select *

  from tblProject

  where projectID = IsNull(@projectID, projectID)

• cidr

  Ten Centuries

  Points: 1353

  More actions

  June 17, 2010 at 8:49 am

  #1181615

  Hey checkai, thanks for the response.

  This is quicker cause it's not doing a whole table scan. It is still doing an index scan but it's quicker than what I had.

  Thanks