dynamic top value and optional parameters in sql server

• whole

  SSC-Addicted

  Points: 436

  More actions

  April 5, 2011 at 9:31 pm

  #248229

  hi,

  i have sql query like below and trying to do search based on the parameters below which are optional...

  so when am running the query my results doesnt look good. what ever i give the parameters values i still get records based on my count number..

  will TOP and optional paramaters gets accurate results ?

  please let me know.

  -------------------

  SELECT TOP (@Top)

  U.UploadSessionHistoryID

  , H.POHeaderID

  , H.RegionID

  , R.RegionName

  , N.PONumber

  from

  POHeader H

  inner join Regions R on H.RegionID = R.RegionID

  inner join PONumber N on H.PONumberID = N.PONumberID

  inner join UploadSessionHistory U on H.UploadSessionHistoryID = U.UploadSessionHistoryID

  where U.ValidationSuccessful = 1

  and isnull(H.Expired,0) = 0

  and H.DeletedDate is null

  and H.PushedToIRMADate is not null

  and U.UploadUserID =CONVERT(VARCHAR, @user-id)

  and ((H.IRMAVendor_ID = @Vendor) or (@Vendor is null))

  and ((H.subteam = @Subteam) or (@Subteam is null))

  and ((H.StoreAbbr = @Store) or (@Store is null))

  and H.PusheToDate BETWEEN ISNULL(@StartDate,H.PushedToDate) AND

  ISNULL(@EndDate,H.PushedToDate)

  order by H.PushedToDate desc

• UMG Developer

  SSChampion

  Points: 13482

  More actions

  April 5, 2011 at 10:57 pm

  #1307803

  That should work and give accurate results. If you think it isn't can you provide the DDL and sample data so that we can see what you are seeing.

  Note: That is what some call a catch-all query and they can often have very poor performance. Here is a blog post about them that you should read: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/