doubt in multiple criteria

Mogamboo_Khush_Hua

Please take a look in both where clause in store procedure

USE [INVENTORY_ MANAGEMENT]
GO
/****** Object: StoredProcedure [dbo].[GetEngagementStandardsByCriteria] Script Date: 02/05/2010 15:25:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

Create PROCEDURE [dbo].[Engagement_Get_StandardsByCriteria ]
-- Add the parameters for the stored procedure here
(
@ClientName NVARCHAR(255)=NULL,
@Year FLOAT=NULL,
@Application NVARCHAR(255) =NULL,
@Database NVARCHAR(255)=NULL,
@OS NVARCHAR(255)=NULL,
@QuickName NVARCHAR(MAX)=NULL

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    ---This query gives me 985 rows when i didn't pass any values to parameter
SELECT \[Client Name\] AS Client,\[Year\],\[Application\],
	   \[Database\],OS,\[MLP Heading\] As Heading,
	   \[Standard Observation\] AS Observation,\[Standard Impact\]AS Impact,
	   \[Standard Recommendation\] AS Recommendation
FROM Engagement\_Standards
WHERE (@ClientName IS NULL OR \[Client Name\]=@ClientName)
	And
      (@Year IS NULL OR \[Year\]=@Year)
	And
	  (@Application IS NULL OR \[Application\]=@Application)
	And
      (@Database IS NULL OR \[Database\]=@Database)
	And
      (@OS IS NULL OR OS=@OS)
	And
      (@QuickName IS NULL OR \[Quick Name\]=@QuickName);

          -- **But this query gives me 956 rows on same criteria mentioned above why it is so**
              
          SELECT Std\_MLPID,\[Client Name\] AS Client,\[Year\],\[Application\],
	   \[Database\],OS,\[MLP Heading\] As Heading,
	   \[Standard Observation\] AS Observation,\[Standard Impact\]AS Impact,
	   \[Standard Recommendation\] AS Recommendation
FROM Engagement\_Standards
WHERE isNull(@ClientName, \[Client Name\]) = \[Client Name\]
AND isNull(@Year, \[Year\]) = \[Year\]
    AND isNull(@Application, \[Application\]) = \[Application\]
AND isNull(@Database, \[Database\]) = \[Database\]
AND isNull(@OS, \[OS\]) = OS
    AND isNull(@QuickName, \[Quick Name\]) = \[Quick Name\];

END

Mycroft Holmes

Here is how I would test this. Change the content to * select * from table this will remove any field requirements comment one of the where lines in each query until you find the discrepancy Once you have identified the clause causing the problem you then need to inspect the data to find the cause. That is a weird where clause, I have never used the 2nd style of isnull, I'd be interested in the resolution you come up with.

Never underestimate the power of human stupidity RAH

WoutL

Could it be that any of the columns in your where clause contains Null values? In that case the second query will not return that rows because the where clause will say

Where null = null

. The firs query however is will say

where null is null or null = null

, which will work.

Wout Louwers

Hesham Amin

I agree to WoutL's post. But I want to clarify that null comparisons fail. unless you have ANSI_NULLS set to OFF. So take care of nulls in your queries. Also check: Another place when NULL gets tricky[^]

Hesham A. Amin My blog twitter: @HeshamAmin

Mogamboo_Khush_Hua

yes it contains null values Can u explain me , how sql server evaluates below clause, if the column contains null value where null is null or null = null and in the case isNull(@Year, [Year]) = [Year]

WoutL

Where null is null or null = null Evaluates to Where true or false Evalutes to Where true where isNull(@Year, [Year]) = [Year] evaluates to (is @year is null and [year] is null) where IsNull(Null, Null) = Null evaluates to Where Null = Null evaluates to Where false I hope this helps

Wout Louwers