Hi I have an sql statement that when i put through the query builder and specify NULL it returns all the rows. if i put 0 it returns nothing.

how can i change the "IS Null" part of the statement to work with 0. so when i specify 0 for all values it returns all the rows.

so i want 0 to work like NULL does 

1    SELECT        LocationID, TypeID, Title, Description, PropertyID, Price,
2                                 (SELECT        Location
3                                   FROM            Location_Table
4                                   WHERE        (Property_Table.LocationID = LocationID)) AS Location,
5                                 (SELECT        TypeOfProperty
6                                   FROM            Type_Table
7                                   WHERE        (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8    FROM            Property_Table
9    WHERE        (@MinPrice IS NULL) AND (@MaxPrice IS NULL) AND (@TypeID IS NULL OR
10                            TypeID = @TypeID) AND (@LocationID IS NULL OR
11                            LocationID = @LocationID) OR
12                            (@MinPrice IS NULL) AND (@TypeID IS NULL OR
13                            TypeID = @TypeID) AND (@LocationID IS NULL OR
14                            LocationID = @LocationID) AND (Price <= @MaxPrice) OR
15                            (@MaxPrice IS NULL) AND (@TypeID IS NULL OR
16                            TypeID = @TypeID) AND (@LocationID IS NULL OR

technically null and 0 are totally different beasts. I remember hearing there was a pitfall about = null comparisons where if you do

where col = null and ....

and you will get nothing or everything.

So, you might have to do extra work like this:

... where

(@minPrice is null or @minprice < price) and (@maxprice is null or @maxprice > price)

isnull(@LocationID,0)

Try

IF @TypeID = 0 SET @TypeID = NULL
SELECT LocationID, TypeID, Title, Description, PropertyID, Price,

 (SELECT        Location       FROM            Location_Table
                                   WHERE        (Property_Table.LocationID = LocationID)) AS Location,
                                 (SELECT        TypeOfProperty   FROM            Type_Table
                                   WHERE        (Property_Table.LocationID = TypeID)) AS TypeOfProperty
    FROM            Property_Table
    WHERE        (@MinPrice IS NULL) AND (@MaxPrice IS NULL) 
 AND (COALSECE(@TypeID, TypeID) = TypeID) 
 AND (COALSECE(@LocationI, Location) = Location) 
-- and so on

The effect of the coalesce is to select on @TypeID if non-zero else all records if it is null. 

you can use the sql nullif() function

where (nullif(@MinPrice,0) is null)

I Can't get any of the answers to work for me
can someone amend my sql statement so i can where to add the new code

stanley310:

you can use the sql nullif() function

 

where (nullif(@MinPrice,0) is null)

Got it to work in the query builder using the above , but when i view the page with the gridview on it i get

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

> I Can't get any of the answers to work for me can someone amend my sql statement so i can where to add the new code

Please post a table definition script for the table(s) that your statement references. If you have posted the script by the time I return to my lodgings tonight, I will have a go at rewriting the TSQL. A few INSERTS would not go amiss.

To produce a script, open the database in SQL Server Management Studio, navigate to the table(s) in object explorer and select produce script. 

TATWORTH:

Please post a table definition script for the table(s) that your statement references. If you have posted the script by the time I return to my lodgings tonight, I will have a go at rewriting the TSQL. A few INSERTS would not go amiss.

To produce a script, open the database in SQL Server Management Studio, navigate to the table(s) in object explorer and select produce script. 

i don't know how to use sql server management studio

my database table structure is

property_table

propertyid (key) int
locationid int
typeid int
title nvarcahr(200)
description nvarchar(max)
price money
bedrooms int allow nulls
image varbinary(max) allow nulls

Location_table

Locationid (key) int
location nvarchar(200)

Type_Table

Typeid (ket) int
location nvarchar(200)

hope this is something you can help me with
 

TATWORTH:

Please post a table definition script for the table(s) that your statement references.

here are the scripts for the 3 tables.

1    USE [C:\USERS\ADAM\DOCUMENTS\VISUAL STUDIO 2008\WEBSITES\ESTATEAGENTSDAL\APP_DATA\PROPS.MDF]
2    GO
3    /****** Object:  Table [dbo].[Location_Table]    Script Date: 05/11/2008 23:57:45 ******/
4    SET ANSI_NULLS ON
5    GO
6    SET QUOTED_IDENTIFIER ON
7    GO
8    CREATE TABLE [dbo].[Location_Table](
9    	[LocationID] [int] IDENTITY(1,1) NOT NULL,
10   	[Location] [nvarchar](200) COLLATE Latin1_General_CI_AS NOT NULL,
11    CONSTRAINT [PK_Location_Table] PRIMARY KEY CLUSTERED 
12   (
13   	[LocationID] ASC
14   )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
15   ) ON [PRIMARY]

1    USE [C:\USERS\ADAM\DOCUMENTS\VISUAL STUDIO 2008\WEBSITES\ESTATEAGENTSDAL\APP_DATA\PROPS.MDF]
2    GO
3    /****** Object:  Table [dbo].[Type_Table]    Script Date: 05/11/2008 23:59:52 ******/
4    SET ANSI_NULLS ON
5    GO
6    SET QUOTED_IDENTIFIER ON
7    GO
8    CREATE TABLE [dbo].[Type_Table](
9    	[TypeID] [int] IDENTITY(1,1) NOT NULL,
10   	[TypeOfProperty] [nvarchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
11    CONSTRAINT [PK_Type_Table] PRIMARY KEY CLUSTERED 
12   (
13   	[TypeID] ASC
14   )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
15   ) ON [PRIMARY]

1    USE [C:\USERS\ADAM\DOCUMENTS\VISUAL STUDIO 2008\WEBSITES\ESTATEAGENTSDAL\APP_DATA\PROPS.MDF]
2    GO
3    /****** Object:  Table [dbo].[Property_Table]    Script Date: 05/11/2008 23:59:22 ******/
4    SET ANSI_NULLS ON
5    GO
6    SET QUOTED_IDENTIFIER ON
7    GO
8    CREATE TABLE [dbo].[Property_Table](
9    	[PropertyID] [int] IDENTITY(1,1) NOT NULL,
10   	[LocationID] [int] NOT NULL,
11   	[TypeID] [int] NOT NULL,
12   	[Title] [nvarchar](200) COLLATE Latin1_General_CI_AS NOT NULL,
13   	[Description] [nvarchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
14   	[Price] [money] NOT NULL,
15   	[Bedrooms] [int] NULL,
16    CONSTRAINT [PK_Property_Table] PRIMARY KEY CLUSTERED 
17   (
18   	[PropertyID] ASC
19   )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
20   ) ON [PRIMARY]
21   

adamjw3:

Hi I have an sql statement that when i put through the query builder and specify NULL it returns all the rows. if i put 0 it returns nothing.

how can i change the "IS Null" part of the statement to work with 0. so when i specify 0 for all values it returns all the rows.

so i want 0 to work like NULL does 

 

 
1       SELECT        LocationID, TypeID, Title, Description, PropertyID, Price,
2                           (SELECT        Location
3                           FROM            Location_Table
4                           WHERE        (Property_Table.LocationID = LocationID)) AS Location,
5                           (SELECT        TypeOfProperty
6                           FROM            Type_Table
7                           WHERE        (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8       FROM            Property_Table
9       WHERE        (@MinPrice = 0) AND (@MaxPrice = 0) AND (@TypeID = 0 OR
10                      TypeID = @TypeID) AND (@LocationID = 0 OR
11                      LocationID = @LocationID) OR
12                      (@MinPrice = 0) AND (@TypeID = 0 OR
13                      TypeID = @TypeID) AND (@LocationID = 0 OR
14                      LocationID = @LocationID) AND (Price <= @MaxPrice) OR
15                      (@MaxPrice = 0) AND (@TypeID = 0 OR
16                      TypeID = @TypeID) AND (@LocationID = 0 OR
17   
18