0

The stored procedure on the sql server is :

ALTER PROCEDURE [dbo].[sp_archive] 
@p1 varchar(21)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * from audit where UpdateDate like '%@p1%'
END

UpdateDate is a varchar(21) field.

In Delphi I have :

procedure TForm5.cxButton1Click(Sender: TObject);
begin
DataModule2.Archive.Params.ParamByName('p1').AsString := datetostr(cxDateEdit1.Date);
DataModule2.Archive.Prepare;
DataModule2.Archive.ExecProc;
end;

And yet,if I select the date in the cxDateEdit1 of which data (date) exists in the audit table,nothing gets displayed. If I run the query on the sql server :

select * from AUDIT where UpdateDate like '%30.12.2017%'

the data is displayed. So I guess there's something wrong with the dateformat the cxdataedit is displaying. Even if I change the display and edit format of the cxdateedit to : dd.mm.yyyy still I can retrieve no data.What am I missing here ?

I also tried :

DataModule2.Archive.Params.ParamByName('p1').AsString := cxDateEdit1.Text;

but to no avail ...

user763539
  • 3,509
  • 6
  • 44
  • 103
  • 4
    In your stored procedure you're limiting by the `%@p1%` string literally, not by the parameter value. This has nothing to do with Delphi. – Victoria Jan 14 '18 at 14:49
  • Have you tried `mm/dd/yyyy` format? That's what Sql Server normally expects. – MartynA Jan 14 '18 at 14:53
  • well its a stored procedure and @p1 is a parameter. I need to cut off the time part so I am using 'like'. – user763539 Jan 14 '18 at 14:54
  • 1
    A safer way is to specify that the date is greater than or equal to the date you want and less than the day affer. – MartynA Jan 14 '18 at 14:56
  • @MartynA its a string field and not date one. Example: The data written to it is: 14.01.2018 14:21 . Since I dont need time but only the date part that is why I am using like. – user763539 Jan 14 '18 at 14:59
  • 4
    Why store dates in a string format instead of date format? You're just over-complicating things by converting it to string. – Jerry Dodge Jan 14 '18 at 15:23
  • 5
    Do not, do not, do not, do not, do not, do not, do not: work with dates as strings. Store them as dates, query using date values, convert input into `TDateTime` values a.s.a.p. and you will save yourself plenty of mistakes and errors. (Not to mention avoid problem when users prefer a different format.) – Disillusioned Jan 14 '18 at 15:45
  • @JerryDodge: Indeed, but imo the main reason using a char field rather than a date field is that it would allow invalid datetimes to be stored. – MartynA Jan 14 '18 at 15:46
  • @MartynA People try that. But the problem with that line of thinking is that the invalid value is still invalid at a later point in time. E.g. Allow invalid birth date, and how do you calculate age for someone with a birthdate of 31 February? Failure to sanitise data simply kicks the problem down the road meaning it has to be dealt with in more places. And if a date is invalid, can any part of the value be trusted? – Disillusioned Jan 14 '18 at 15:51
  • @CraigYoung Exactly, I wasn't meaning to suggest for one moment that a char field *should* be used for dates, far from it. Using a char field for dates pretty much guarantees that you can never trust the data in it. – MartynA Jan 14 '18 at 16:10

2 Answers2

0

Got it to work. Changed stored procedure to :

SELECT * from audit where UpdateDate like + @p1 + '%'

Changed cxDateEdit display and edit format to :

dd.mm.yyyy

In Delphi I used :

DataModule2.Archive.Params.ParamByName('p1').AsString := cxDateEdit1.Text;

user763539
  • 3,509
  • 6
  • 44
  • 103
  • 2
    I must say I'm not convinced. That change you made to the stored proc should not have had an impact. Tip for future. Narrow down the problem first to be sure you know exactly what is going wrong. Otherwise you end up with bad code full of faux workarounds for faux problems. E.g. Here you could have tested Calling without using the `cxDateEdit1` component. I.e. pass a hard-coded string. This would confirm that the proc behaves correctly, and the parameter passing works correctly. Then debug to find out what format `cxDateEdit1` is actually using. – Disillusioned Jan 14 '18 at 16:00
  • 5
    Either way, I must repeat that using dates as strings is shooting yourself in the foot in far too many ways. Only one of which is the errors and bugs you'll have to deal with. – Disillusioned Jan 14 '18 at 16:02
  • 2
    I found an example here : https://stackoverflow.com/questions/16380196/using-like-operator-with-stored-procedure-parameters#16381750 Victoria got me thinking that perhaps I am indeed passing the string literally and not as parameter.Tried and it worked. – user763539 Jan 14 '18 at 19:32
  • Aah. Good. That means the stored proc called directly from within Sql Server Management Studio would not have worked either; and clear evidence there's a mistake there. (Same narrow down principle) – Disillusioned Jan 14 '18 at 21:09
0

Put % in quotes and concatenate your variable @p1 between them like below

SELECT * from audit where UpdateDate like '%'+@p1+'%'
F. Müller
  • 3,969
  • 8
  • 38
  • 49
Javed Lasi
  • 11
  • 2