Retrieving ALL from column in SQL database

[abathurst]

I have a drop down menu on a page where the user can select between, All, House, Unit and Rural which send the selection to a stored procedure which access' the database and returns the information with the selection eg. House from the category column.

It all works great when you select House, Unit or Rural and the trouble I am having is when you select All it returns no information. I'm sure there is something very simple that I am missing but your help would be great.

Here is the stored procedure code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Search_Realestate] 
(
	
	@RegID as varchar(50),
	@category as varchar(50)
) 

as

select 
headline as headline,
subNumber as subNumber,
streetNumber as streetNumber,
street as street,
suburb as suburb,
'$' + price as price,
bedrooms as bedrooms,
bathrooms as bathrooms,
garages as garages,
description as description,
image1 as URL1, 
category as category,

Ref_No as RefNo,
Rowguid
from Realestate
Where @RegID = 'Admin' and category = @category

And here is the to pass the information to the stored procedure:

If DDLBuildingType.Text = "All" Then

            '@category as varchar(50),
            Session("Category") = "'house' or category = 'unit' or category = 'rural'" 
           '----------------------------------------
            Dim parameterCategory As SqlParameter = New SqlParameter("@category", SqlDbType.VarChar, 50)
            parameterCategory.Value = Session("category")
            myCommand.SelectCommand.Parameters.Add(parameterCategory)

        Else

            '@category as varchar(50),
            Session("Category") = DDLBuildingType.Text
            '----------------------------------------
            Dim parameterCategory As SqlParameter = New SqlParameter("@category", SqlDbType.VarChar, 50)
            parameterCategory.Value = Session("category")
            myCommand.SelectCommand.Parameters.Add(parameterCategory)

        End If

The part in red is where I believe the issue is. I have tried it just as "house" and that return all of the houses but I need it to return everything. Thanks in advanced.

[urtrivedi]

better if you keep condition in your stored procedure and not in your front end

'@category as varchar(50),
Session("Category") = DDLBuildingType.Text
'----------------------------------------
Dim parameterCategory As SqlParameter = New SqlParameter("@category", SqlDbType.VarChar, 50)
parameterCategory.Value = Session("category")
myCommand.SelectCommand.Parameters.Add(parameterCategory)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Search_Realestate] 
(
	
	@RegID as varchar(50),
	@category as varchar(50)
) 

as

if @cateogry='All' 
begin
	select 
	headline as headline,
	subNumber as subNumber,
	streetNumber as streetNumber,
	street as street,
	suburb as suburb,
	'$' + price as price,
	bedrooms as bedrooms,
	bathrooms as bathrooms,
	garages as garages,
	description as description,
	image1 as URL1, 
	category as category,
	
	Ref_No as RefNo,
	Rowguid
	from Realestate
	Where @RegID = 'Admin' 
end
else
begin

	select 
	headline as headline,
	subNumber as subNumber,
	streetNumber as streetNumber,
	street as street,
	suburb as suburb,
	'$' + price as price,
	bedrooms as bedrooms,
	bathrooms as bathrooms,
	garages as garages,
	description as description,
	image1 as URL1, 
	category as category,
	
	Ref_No as RefNo,
	Rowguid
	from Realestate
	Where @RegID = 'Admin' and category = @category

end

[abathurst]

Of course. You a champ. Friday afternoon and my brain just shuts down. Thanks so much.