Home End User

Date Parameter in SQL Stored Procedure call

I have a rather complicated data collection process I need to run that takes a single date so I need to call an Microsoft SQL Stored Procedure to do the work.

I have the DataSetting.DatabaseType set correctly to dtMSSQLServer in the ppDesigner and the parm type is dtDate.

If I try to call it using 'EXEC dbo.myprocedure :MyDate' I get a 'Incorrect syntax near the keyword CONVERT' error from the designer.

If I use a simple 'Select * From sometable Where MyDate = :MyDate' then it works fine.

If I change the parameter type to a dtString it works for both the Select and the EXEC.

Any thoughts? I would like to keep the parm a date type to avoid invalid dates when prompted.
Everyone has a photographic memory, but not everyone has film.

Comments

  • Hi Tom,

    RB is using an expression to normalize the date. From Google, MS SQL does not accept expression for a Stored Proc param value.

    Have a look at TdaSQL.FormatParamValue (daSQL.pas). Try commenting out the 'if (IsMSSQLServer) then..' block of code towards the bottom. Test and see whether that works.








    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
Sign In or Register to comment.