0

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

  • 2
    Your passing the parameters by position not name and missing the first one. – Martin Smith Jan 25 '17 at 05:56
  • You have to build like "@city=@City " + http://stackoverflow.com/questions/15191517/sql-server-stored-procedure-parameters – Habeeb Jan 25 '17 at 06:33
  • 1
    Why are you doing it this way anyway rather than using ADO.NET Parameters? You haven't shown us the code dealing with strings like city and postcode but it may well be vulnerable to SQL injection. – Martin Smith Jan 25 '17 at 06:35

0 Answers0