I have a stored procedure that uses saledatestart/saledateend to filter the selection of rows to process:
string buildCS =
@"CREATE PROCEDURE usp_combinedsearch
@SaleId nvarchar(25) = NULL,
@City nvarchar(50) = NULL,
@ZipCode nvarchar(10) = NULL,
@County nvarchar(25) = NULL,
@JudgementAmountMin money = NULL,
@JudgementAmountMax money = NULL,
@AssessedValueMin money = NULL,
@AssessedValueMax money = NULL,
@saledatestart Date = NULL,
@saledateend Date = NULL
AS
SELECT s.SaleId, s.CaseNumber, s.County, s.Mapsco, s.Plaintiff, s.Defendant,
s.Address, s.City, s.State, s.ZipCode, s.JudgementAmount, s.YearOfConstruction,
s.LegalDescription, s.AssessedValue
FROM Sale s
WHERE (s.SaleId = @SaleId or @SaleId IS NULL)
AND (s.City = @City OR @City IS NULL)
AND (s.ZipCode = @ZipCode OR @ZipCode IS NULL)
AND (s.County = @County OR @County IS NULL)
AND (s.JudgementAmount >= @JudgementAmountMin OR @JudgementAmountMin IS NULL)
AND (s.JudgementAmount <= @JudgementAmountMax OR @JudgementAmountMax IS NULL)
AND (s.AssessedValue >= @AssessedValueMin OR @JudgementAmountMin IS NULL)
AND (s.AssessedValue <= @AssessedValueMax OR @AssessedValueMax IS NULL)
AND ('s.SaleDate' >= '@saledatestart' OR '@saledatestart' IS NULL)
ORDER BY s.SaleId
OPTION (RECOMPILE)";
I call it with:
if (!string.IsNullOrEmpty(TbAssessedValueMin.Text))
{
build =
build.Append("DECLARE @AssessedValueMin money " + "= '" +
decimal.Parse(TbAssessedValueMin.Text, NumberStyles.Currency) + "' ");
}
else
{
build = build.Append("DECLARE @AssessedValueMin money ");
}
if (!string.IsNullOrEmpty(TbAssessedValueMax.Text))
{
build =
build.Append("DECLARE @AssessedValueMax money " + "= '" +
decimal.Parse(TbAssessedValueMax.Text, NumberStyles.Currency) + "' ");
}
else
{
build = build.Append("DECLARE @AssessedValueMax money ");
}
if (_saledateStart != DateTime.MinValue)
{
build =
build.Append("DECLARE @saledatestart date " + "= '" + _saledateStart + "' ");
}
else
{
build = build.Append("DECLARE @saledatestart date ");
}
if (_saledateEnd != DateTime.MinValue)
{
build =
build.Append("DECLARE @saledateend date " + "= '" + _saledateEnd + "' ");
}
else
{
build = build.Append("DECLARE @saledateend date ");
}
build = build.Append("EXECUTE @RC = [dbo].[usp_combinedsearch] " +
"@City " +
",@ZipCode " +
",@County " +
",@JudgementAmountMin " +
",@JudgementAmountMax " +
",@AssessedValueMin " +
",@AssessedValueMax " +
",@saledatestart " +
",@saledateend " +
";");
The row in SQL Server is SaleDate defined as date, not null. When I run the stored procedure I get Operand type clash date is incompatible with money. If I remove all references to dates from the procedure it works fine. So it would seem that the problem lies in how I am trying to deal with saledate, but I can't seem to figure out what I am doing wrong. Any help would be appreciated.
Tom