Passing parameter values to a SQL 2005 where statement 1

[stapet]

Hello all....I am stumped! I recently upgraded from SQL Ser ver 2000 to SQL Server 2005. I change to some code in one of my Stored Procedures after the upgrade and found that passing a parameter value to a WHERE statement no longer works. I have been researching this for 2 days, and not sure how to get around it. Can someone point me in the right direction for help! Thanks in advance....

Here is my code...
ALTER PROCEDURE [dbo].[USP_SelAccrualInfo] @begCompany varchar(300) = NULL,
@endCompany varchar (300) = NULL,
@begInvDate datetime = NULL,
@endInvDate datetime = NULL,
@begOrderDate datetime = NULL,
@endOrderDate datetime = NULL,
@div varchar(1)
AS

DECLARE @wherestring varchar (3000)
DECLARE @sql varchar (3000)

SET @wherestring = 'DataModesDaily.dbo.viewAccrualInfo.COMPANY >= "'+ @begCompany +'"'+' AND DataModesDaily.dbo.viewAccrualInfo.COMPANY <= "'+@endCompany +'"'

IF @begInvDate IS NOT NULL AND @endInvDate IS NOT NULL
BEGIN
SET @wherestring = @wherestring + ' AND DataModesDaily.dbo.viewAccrualInfo.INV_DATE >= "'+ CONVERT(varchar(10), @begInvDate, 101)+'"'+
' AND DataModesDaily.dbo.viewAccrualInfo.INV_DATE <= "' +CONVERT(varchar(10), @endInvDate, 101)+'"'
END

IF @begOrderDate IS NOT NULL AND @endOrderDate IS NOT NULL
BEGIN
SET @wherestring = @wherestring + ' AND DataModesDaily.dbo.viewAccrualInfRDER_DATE >= "' +CONVERT(varchar(10), @begOrderDate, 101)+'"'
+ ' AND DataModesDaily.dbo.viewAccrualInfRDER_DATE <= "'+CONVERT(varchar(10), @endOrderDate, 101) + '"'
END

SET @wherestring = @wherestring + ' AND DataModesDaily.dbo.viewAccrualInfo.DIV = "' + @div + '"'


SET @sql= ' DECLARE AccrualCursor CURSOR FOR' +
' SELECT DataModesDaily.dbo.viewAccrualInfo.COMPANY, DataModesDaily.dbo.viewAccrualInfo.B_LIST, DataModesDaily.dbo.viewAccrualInfo.INV_DATE,
DataModesDaily.dbo.viewAccrualInfo.INV_NO, DataModesDaily.dbo.viewAccrualInfo.SHIP_DATE,
DataModesDaily.dbo.viewAccrualInfo.CUST_PO_NO, DataModesDaily.dbo.viewAccrualInfRDER_NO, DataModesDaily.dbo.viewAccrualInfRDER_DATE,
DataModesDaily.dbo.viewAccrualInfo.CHARGES, DataModesDaily.dbo.viewAccrualInfo.PRICE_FACTOR, DataModesDaily.dbo.viewAccrualInfo.QTY,
DataModesDaily.dbo.viewAccrualInfo.ITEM_NO, DataModesDaily.dbo.viewAccrualInfo.PRICED, DataModesDaily.dbo.viewAccrualInfo.ACCOUNT,
DataModesDaily.dbo.viewAccrualInfo.STOCK_UNIT ' +
' FROM DataModesDaily.dbo.viewAccrualInfo' + ' WHERE' + ' ' + @wherestring + ' OPEN AccrualCursor'

print @wherestring

EXEC (@sql)




This is the value of @wherestring...

DataModesDaily.dbo.viewAccrualInfo.COMPANY >= "A" AND DataModesDaily.dbo.viewAccrualInfo.COMPANY <= "B" AND DataModesDaily.dbo.viewAccrualInfo.INV_DATE >= "10/01/2009" AND DataModesDaily.dbo.viewAccrualInfo.INV_DATE <= "10/31/2009" AND DataModesDaily.dbo.viewAccrualInfo.DIV = "1"

Thank you!

 

[bborissov]

And that is worked in 2000?


You don't need a Dynamic SQL at all.

DECLARE AccrualCursor CURSOR FOR
SELECT DataModesDaily.dbo.viewAccrualInfo.COMPANY,
       DataModesDaily.dbo.viewAccrualInfo.B_LIST, 
       DataModesDaily.dbo.viewAccrualInfo.INV_DATE,
       DataModesDaily.dbo.viewAccrualInfo.INV_NO, 
       DataModesDaily.dbo.viewAccrualInfo.SHIP_DATE,
       DataModesDaily.dbo.viewAccrualInfo.CUST_PO_NO, 
       DataModesDaily.dbo.viewAccrualInfo.ORDER_NO, 
       DataModesDaily.dbo.viewAccrualInfo.ORDER_DATE,
       DataModesDaily.dbo.viewAccrualInfo.CHARGES, 
       DataModesDaily.dbo.viewAccrualInfo.PRICE_FACTOR, 
       DataModesDaily.dbo.viewAccrualInfo.QTY,
       DataModesDaily.dbo.viewAccrualInfo.ITEM_NO,
       DataModesDaily.dbo.viewAccrualInfo.PRICED,  
       DataModesDaily.dbo.viewAccrualInfo.ACCOUNT,
       DataModesDaily.dbo.viewAccrualInfo.STOCK_UNIT
FROM DataModesDaily.dbo.viewAccrualInfo
WHERE DataModesDaily.dbo.viewAccrualInfo.COMPANY BETWEEN @begCompany AND @endCompany
  AND ( (@begInvDate IS NULL OR @endInvDate IS NULL) OR
        (DataModesDaily.dbo.viewAccrualInfo.INV_DATE >=  @begInvDate AND 
         DataModesDaily.dbo.viewAccrualInfo.INV_DATE <=  @endInvDate))
  AND ( (@begOrderDate IS NULL OR @endOrderDate IS NULL) OR
        (DataModesDaily.dbo.viewAccrualInfo.ORDER_DATE >= @begOrderDate AND 
         DataModesDaily.dbo.viewAccrualInfo.ORDER_DATE <= @endOrderDate))

OPEN AccrualCursor

NOT TESTED! And count the brackets I didn't

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[stapet]

Yes, It was working in 2000.

Can you explain this part of the query for me?

(@begInvDate IS NULL OR @endInvDate IS NULL)

Is that checking to see if @begInvDate and @endInvDate is null?

I'm not understanding the purpose of that statement.

When I changed this I get data with invoice dates going back to 2006, when entering a @begInvDate of '10/01/2009' and @endInvDate of '10/15/2009'.

Thanks again.

 

[stapet]

Never mind....

I had forgot a ).

This seems to work great! But could you still explain what is happening on that line of code?

Thanks a bunch!!!

 

[bborissov]

I'll try, but be warned, I have no George's explanation skills
That is how SQL Server interprets the AND and OR.
If OR is included the If the first part returns True other parts are not evaluated. The same for AND, if the first returns False other didn't evaluated also.
The AND and OR are evaluated from left to right.
So with this:

(@begInvDate IS NULL OR @endInvDate IS NULL) OR
(DataModesDaily.dbo.viewAccrualInfo.INV_DATE >=  @begInvDate AND
         DataModesDaily.dbo.viewAccrualInfo.INV_DATE <=  @endInvDate))

If either of @begInvDate and @endInvDate is NULL the the whole statement returns True and the part where INV_DATE is tested is not checked at all. If both are NOT NULL then we go to the INV_DATE part.
That is why it is important when the OR and AND is involved in ONE statement to place brackets over the statements you want to check at once.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

BTW, I'm wondering where George is? I know he has been on vacation, but his silence started to worry me...

 

[bborissov]

If I'm vacation that means: "Stay away from computer"

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.