is there another way to shorten this? thanks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

is there another way to shorten this? thanks

Author

Topic

j0shua
Starting Member

40 Posts

Posted - 2008-12-21 : 23:43:06

is there another way to shorten this? as you can see there is only one line difference between the select statements. thank you so much. help is very much appreciated.

IF @fdDateFrom IS NOT NULL 
  BEGIN
	SELECT     fxKeyBill, fxKeySupplier, fxKeyCompany,
			 fxKeyTerms, fcMemo, fdTransDate, 
		      fnAmountDue, fdDueDate, fcRefNo,
			 fbBillReceived, fbBillType,  fuCreatedby, 
		      fdDateCreated, fuUpdatedby, fdDateUpdated,
			 fdDiscDate, fbPosted, fbDeleted, 
		      fdCount, fcCVNo, fbPrinted, 
			 fdAmttoPay, fdCreditAmt, fdDiscountAmt, 
		      fcReviewed, fcReferenceNo, fcCenterCode, 
			 fcAPVno, fxKeyPayBills, fcType, fbPaid
	FROM       tBills
	
	WHERE fxKeyCompany = @fxKeyCompany
	AND fdTransDate BETWEEN  @fdDateFrom AND @fdDateTo
  END

ELSE

  BEGIN
	SELECT     fxKeyBill, fxKeySupplier, fxKeyCompany,
			 fxKeyTerms, fcMemo, fdTransDate, 
		      fnAmountDue, fdDueDate, fcRefNo,
			 fbBillReceived, fbBillType,  fuCreatedby, 
		      fdDateCreated, fuUpdatedby, fdDateUpdated,
			 fdDiscDate, fbPosted, fbDeleted, 
		      fdCount, fcCVNo, fbPrinted, 
			 fdAmttoPay, fdCreditAmt, fdDiscountAmt, 
		      fcReviewed, fcReferenceNo, fcCenterCode, 
			 fcAPVno, fxKeyPayBills, fcType, fbPaid
	FROM       tBills
	
	WHERE fxKeyCompany = @fxKeyCompany
  END

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-22 : 00:00:44

U Can use OR operator like


AND (@fdDateFrom IS NULL OR fdTransDate BETWEEN  @fdDateFrom AND @fdDateTo)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

j0shua
Starting Member

40 Posts

Posted - 2008-12-22 : 00:08:33

Thanks, but error comes in from when the value of fdDateFrom is null.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-22 : 00:10:39

can u post the error message and complete code which u r runing

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

j0shua
Starting Member

40 Posts

Posted - 2008-12-22 : 00:26:55

thanks it works! i juz misspelled one parameter causing the error, but now your solution works! i will now test it on different scenarios. thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources