Hi Gerard,

I am glad we clear this one out.


Yeah, I loved VFP Macro Substitution, and it has been hard working without it in some situations but MS-SQL is a totally different animal than VFP when dealing with data, and the things that can be done with TSQL and SQL Engine are just amazing when compared with the way we did it back then with VFP.

Edhy Rijo

Hi Edhy.

I have this working now. Thanks for all your help

I intially had though that Parameter Substitiution worked as I had also some Paranater Substitiution on for Character based Fields (as in @MyCharFieldName >= 'SomeString')

I 'thought' that this was workong as no error was beong generated, but now realise all that was happenong was that the Charcater String --> MyCahrFiledName was being compared to the sting ---> SomeString.   I was locked in a VFP timewarp and assmed that SQL SProcs worked in a similar way to VFP Macro Substitution.

Regards,

Gerard

Hi Gerard,



Now I understand if you would have posted the whole stored procedure before, we would have figure this one out faster

You have to use a CASE statement in your sp WHERE condition to manage this.  In the sample code below, you have to populate the parameter @MyDateFieldName with the expected value used in the CASE WHEN of the sp.  All this is nice, but be aware that you should test all possible conditions and make sure your table ITR has the recommended indexes to optimize these WHERE conditions.

Have fun!!!

ALTER PROCEDURE [dbo].[TestSPR]

 @Paramdate Date,

 @MyDateFieldName VARCHAR

AS

BEGIN

    SET NOCOUNT ON;

    SELECT

         *

    FROM

         ITR

    WHERE

         CASE @MyDateFieldName

         WHEN 'OrderDate' THEN ITR_ORDDAT

         WHEN 'InvoiceDate' THEN ITR_INVDAT

         END >= @ParamDate

END

Edhy Rijo

Hi Edhy. I have established what the problem here is , but dont know how to fix it.

In my Select Command, depending on a parameter passed into the stored procedure, i need to change the Field from the Table that is used. Sometimes it may be the Order date, sometimes the Despatch date and sometimes the Invoice date. I pass in a parmater and then have a series of Ifs to store the filed name into a variable. Then, because I am using the variable in the Select string, and not the actual Field name, this gives the error. Here is an example of a cut doen SP which will give an error:

ALTER PROCEDURE [dbo].[TestSPR]
 @Paramdate Date
AS
BEGIN
 SET NOCOUNT ON;
    --SELECT * FROM ITR WHERE ITR_ORDDAT >= @Paramdate      (This command works Fine)
   
    DECLARE @Mydate VARCHAR
    set @MyDate = 'ITR_ORDDAT'
    SELECT * FROM ITR WHERE @MyDate >= @Paramdate              // This Line gives an error
END

So if I use SELECT * FROM ITR WHERE ITR_ORDDAT >= @Paramdate ...... works fine

If I store 'ITR_ORDDAT' into @MyDate and run the select (as above) , gives an conversion string to date/Time error.

Hi Gerard,

I guess you want to be able to pass a date to filter the data, but if the date is 1800-1-1, then to ignore that condition.  If that is the case, then try casting the '1800-01-01' to a date value as shown below and that should take care of the error.

    AND (@DateQuery >= @DateFrom OR @DateFrom = cast('1800-01-01' as date)
    AND  @DateQuery <= @DateTo   OR @DateTo   = cast('1800-01-01' as date))

Also, pay attention to the open and close parenthesis, since you are trying to filter by a date condition, the whole expression should be enclosed in a parenthesis, so it will be a single condition.

Edhy Rijo

Hi Edhy. Main bits of my SProc is:

  SELECT * from ITR
   WHERE
   ((@StageOperator = '=' AND ITR_STAGE = @StageQuery ) or (@StageOperator = '>=' and ITR_STAGE >= @StageQuery ) )
    AND (ITR_POST = @PostQuery)
    AND (ITR_CUSREF >= @AccountFrom OR Len(LTRIM(RTRIM(@AccountFrom))) = 0)
    AND (ITR_CUSREF <= @AccountTo    OR Len(LTRIM(RTRIM(@AccountTo))) = 0)
    AND (@TranQuery >= @TranFrom     OR Len(Ltrim(Rtrim(@TranFrom)))  = 0)
    AND (@TranQuery <= @TranTo         OR Len(Ltrim(Rtrim(@TranTo)))  = 0)
    AND (@DateQuery >= @DateFrom     OR @DateFrom = '1800-01-01' )
    AND (@DateQuery <= @DateTo         OR @DateTo   = '1800-01-01')
    OPTION (RECOMPILE)

  Suspect the fact that  have 'Datefrom = '1800-01-01' is causing it to be treated as a string, and I dont get  error if I ensure date is this format when called from SSMS

Nope, if the error is coming from the Stored Procedure, please paste the SP here or if it is too long, then copy it to a text document and attach it here.

Also did you try running the sp in MSMS (Microsoft SQL Server Management Studio)? if so, did it give you an error there too?

Edhy Rijo

Hi Edhy.... do you mean put a break in the Stored Procedure Code (Standard TSQL) where the error is happening ....didnt know you could do that ??

Put a break in your code and do a screenshot of the code in the debugger where it breaks, also please copy and paste the code here.  It looks like your date or the value you are sending is a string and not a date.

Edhy Rijo

Sorry previos post does not seem to have attachment....attachemnt now