Dyanamic Where Clause

Can anyone advise how i resolve this in SQL, I have the following Stored
procedure :
CREATE PROCEDURE sp_jp_report_won_lost_open_test
@.datestart as varchar(12),
@.dateend as varchar(12),
@.Region as varchar(25), -- e.g. Scotland
@.Territory as int, --ie 901232
@.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
@.mv as money, -- eg Minimum Value of Project
@.mx as money -- eg Maximum Value of Project
AS
set nocount on
If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
Begin
SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
p.SpecifierCompanyDisplayName, p.Notes
FROM dbo.TblProject p INNER JOIN
dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
OUTER JOIN
(SELECT LISANo, MIN(QuoteDate) QuoteDate,
MIN(QuoteNo) QuoteNo
FROM tblquotation_header
WHERE (TblQuotation_Header.TotalValue <=
@.mx AND TblQuotation_Header.TotalValue >= @.mv)
GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
OUTER JOIN
dbo.TblQuotation_Header q ON x.QuoteDate = q.QuoteDate
AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
(p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
(p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
AND (t.CurrencySymbol = @.Region)
ORDER BY t.Description
End
If @.mv>0 then i want the additional clause "WHERE (is not null q.TotalValue)
" included in the SQL statement, how do i acheive this ?
Thanks in anticipation
JohnJohn
http://www.sommarskog.se/dyn-search.html
"John" <topguy75@.hotmail.com> wrote in message
news:43a954bf$0$23290$db0fefd9@.news.zen.co.uk...
> Can anyone advise how i resolve this in SQL, I have the following Stored
> procedure :
> CREATE PROCEDURE sp_jp_report_won_lost_open_test
> @.datestart as varchar(12),
> @.dateend as varchar(12),
> @.Region as varchar(25), -- e.g. Scotland
> @.Territory as int, --ie 901232
> @.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
> @.mv as money, -- eg Minimum Value of Project
> @.mx as money -- eg Maximum Value of Project
> AS
> set nocount on
> If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
> Begin
> SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
> p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
> p.SpecifierCompanyDisplayName, p.Notes
> FROM dbo.TblProject p INNER JOIN
> dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
> OUTER JOIN
> (SELECT LISANo, MIN(QuoteDate) QuoteDate,
> MIN(QuoteNo) QuoteNo
> FROM tblquotation_header
> WHERE (TblQuotation_Header.TotalValue <=
> @.mx AND TblQuotation_Header.TotalValue >= @.mv)
> GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
> OUTER JOIN
> dbo.TblQuotation_Header q ON x.QuoteDate =
> q.QuoteDate AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
> WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
> (p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
> (p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
> AND (t.CurrencySymbol = @.Region)
> ORDER BY t.Description
> End
>
> If @.mv>0 then i want the additional clause "WHERE (is not null
> q.TotalValue) " included in the SQL statement, how do i acheive this ?
> Thanks in anticipation
> John
>|||Use dynamic SQL
Look at
http://www.sommarskog.se/dynamic_sql.html
Regards
Amish|||"John" <topguy75@.hotmail.com> wrote in message
news:43a954bf$0$23290$db0fefd9@.news.zen.co.uk...
> Can anyone advise how i resolve this in SQL, I have the following Stored
> procedure :
> CREATE PROCEDURE sp_jp_report_won_lost_open_test
> @.datestart as varchar(12),
> @.dateend as varchar(12),
> @.Region as varchar(25), -- e.g. Scotland
> @.Territory as int, --ie 901232
> @.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
> @.mv as money, -- eg Minimum Value of Project
> @.mx as money -- eg Maximum Value of Project
> AS
> set nocount on
> If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
> Begin
> SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
> p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
> p.SpecifierCompanyDisplayName, p.Notes
> FROM dbo.TblProject p INNER JOIN
> dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
> OUTER JOIN
> (SELECT LISANo, MIN(QuoteDate) QuoteDate,
> MIN(QuoteNo) QuoteNo
> FROM tblquotation_header
> WHERE (TblQuotation_Header.TotalValue <=
> @.mx AND TblQuotation_Header.TotalValue >= @.mv)
> GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
> OUTER JOIN
> dbo.TblQuotation_Header q ON x.QuoteDate =
> q.QuoteDate AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
> WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
> (p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
> (p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
> AND (t.CurrencySymbol = @.Region)
> ORDER BY t.Description
> End
>
> If @.mv>0 then i want the additional clause "WHERE (is not null
> q.TotalValue) " included in the SQL statement, how do i acheive this ?
> Thanks in anticipation
> John
One quick fix could be this:
IF @.mv>0
SELECT ...
WHERE...
AND q.TotalValue is not null
..
ELSE
SELECT "your query as is"|||where ...
and ((@.mv<=0) or (q.TotalValue is not null) )|||Thanks Alex, simple but effective :-)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1135183436.404303.48580@.z14g2000cwz.googlegroups.com...
> where ...
> and ((@.mv<=0) or (q.TotalValue is not null) )
>