Regarding null value in store procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Regarding null value in store procedure

Author

Topic

stephen.prakhasham
Starting Member

3 Posts

Posted - 2008-05-15 : 03:30:38

i am using csFindProperty store procedure in my application. here there are several input parameter are there. my procedure structure are:

ALTER PROCEDURE [dbo].[csFindProperty]
@HouseNumber varchar(10),
@Street varchar(100),
@City varchar(100),
@State varchar(2),
@Zip varchar(15),
@County varchar(25),
@SquareFootageStart int,
@SquareFootageEnd int,
@Bedrooms int,
@Bathrooms decimal(18, 2),
@Garage decimal(18, 0),
@Basement bit,
@StyleID int,
@Subdivision varchar(50),
@SchoolDistrict varchar(50),
@MonthlyRentStart money,
@MonthlyRentEnd money,
@ListPriceStart money,
@ListPriceEnd money,
@PetsAllowed bit
AS
select distinct hd.housenumber,hd.street,hd.city,hd.state,hd.zip, rd.monthlyrent,sd.listdate, sd.listprice from housedata hd, rentdata rd,
saledata sd where hd.HouseNumber like @HouseNumber and hd.Street like @Street and hd.City like @City and hd.State like @State
and hd.Zip like @Zip and hd.County like @County and hd.SquareFootage >= @SquareFootageStart and hd.SquareFootage <= @SquareFootageEnd
and hd.Bedrooms like @Bedrooms and hd.Bathrooms like @Bathrooms and hd.Garage like @Garage and hd.Basement like @Basement
and hd.StyleID like @StyleID and hd.Subdivision like @Subdivision and hd.SchoolDistrict like @SchoolDistrict and rd.MonthlyRent >=@MonthlyRentStart
and rd.MonthlyRent <= @MonthlyRentEnd and sd.ListPrice >= @ListPriceStart and sd.ListPrice <= @ListPriceEnd and rd.PetsAllowed like @PetsAllowed
and hd.HouseDataID=sd.HouseDataID

Execute procedure structure:

DECLARE @return_value int

EXEC @return_value = [dbo].[csFindProperty]
@HouseNumber = N'1733',
@Street = N'N 40th',
@City = N'Kansas City',
@State = N'KS',
@Zip = N'%',
@County = N'Wyandotte',
@SquareFootageStart = 0,
@SquareFootageEnd = 0,
@Bedrooms = 2,
@Bathrooms = 1.00,
@Garage = 0,
@Basement = false,
@StyleID = 0,
@Subdivision = N'',
@SchoolDistrict = N'%',
@MonthlyRentStart = 1025.00,
@MonthlyRentEnd = 1050.00,
@ListPriceStart = 500.00,
@ListPriceEnd = 1000.00,
@PetsAllowed = false

SELECT 'Return Value' = @return_value

GO

if the user enter in the column HouseNumber=1733 that value passed in sp @HouseNumber = N'1733', suppose user can not type any value in the column HouseNumber means, i assign the value HouseNumber='%'. HouseNumber is a varchar datatype.

what my question is: if the column datatype money or int or bit means what is the value i assign @Bedrooms = 2, this situation. since bedrooms is int datatype. if i assign @Bedrooms ='%', conversion error will occur. pls any one can help me come out this problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 03:59:25

There are couple of problems with this.

1. Its better to pass value as NULL for parameters when you dont need filter on a value and do filteration based on it.

For example if you pass @HouseNumber = N'1733' it will give you rows with hd.HouseNumber ='1733' anf when you pass NULL it will give you all house numbers. for this you jsut need to cahnge

where hd.HouseNumber like @HouseNumber
to
where (hd.HouseNumber=@HouseNumber or @HouseNumber is null)
similarly you can apply for all other parameters regardless whether they are nvarchar or int.Make sure you put paranthesis.

2.You tend to return a value from stored procedure but you dont have an OUTPUT parameter specified.I think you need to have an OUTPUT parameter and return value through it.

stephen.prakhasham
Starting Member

3 Posts

Posted - 2008-05-15 : 04:46:12

Thanks your kind reply. if use this query where (hd.HouseNumber=@HouseNumber or @HouseNumber is null) it does return values where HouseNumber have some value. it HouseNumber is null does not return value. what my requirement is HouseNumber have some value but user does not set value for that. so in varchar column we can specify the HouseNumber='%' but i specify the @Basement = '%', it return error message. because Basement is Bit data type. so Is there any symbal like % for int, bit datatype?

stephen.prakhasham
Starting Member

3 Posts

Posted - 2008-05-15 : 06:58:35

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 10:11:14

quote:
Originally posted by stephen.prakhasham

Thanks your kind reply. if use this query where (hd.HouseNumber=@HouseNumber or @HouseNumber is null) it does return values where HouseNumber have some value. it HouseNumber is null does not return value. what my requirement is HouseNumber have some value but user does not set value for that. so in varchar column we can specify the HouseNumber='%' but i specify the @Basement = '%', it return error message. because Basement is Bit data type. so Is there any symbal like % for int, bit datatype?

(hd.HouseNumber=@HouseNumber or @HouseNumber is null) condition will ensure that if you pass a value for @HouseNumber ex. 1733 it will perform check hd.HouseNumber=1733 and return only records satisfying this. If you didnt send any value, it will take the default value which is NULL(You need to give this in definition and it wont perform the earlier and will give records regardless of what housenumber was. I believe that was exactly what you were looking at.Similarly, for other fields too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources