Date Parameter in SQL Stored Procedure call

tchamberlain42

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.

nardmoseley

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.