Profile

Wednesday, 23 May 2012

DECLARE: apply value in multiple places

 What if you need to specify a value such as date in multiple places in a query? Try DECLARE The following example using DECLARE so that you only need to change the date range at one place it will apply to both order date and ship date statements in the WHERE clause below.

DECLARE @qstartdate date, @qenddate date
            SET @qstartdate = '2012-01-01'
            SET @qenddate = '2012-01-31'

SELECT *
FROM dbo.ORDER_ENTRY order

WHERE
order.ORDER_DATE BETWEEN CAST(@qstartdate as DATE) AND CAST(@qenddate as DATE)
OR 
order.SHIPPING_DATE BETWEEN CAST(@qstartdate as DATE) AND CAST(@qenddate as DATE)