Problem in a dynamic stored procedurehelp

I create a stored procedure as following:
......

CREATE Procedure myAutoSearch
(
@.Make varchar(50),
@.Model varchar(50),
@.AutoType varchar(50),
@.Miles float,
@.Zipcode varchar(5)
)
AS

DECLARE @.RowCount int
SELECT @.RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @.Zipcode AND CityType = 'D'

if @.RowCount > 0
BEGIN

SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@.ZIPCode,@.Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @.Make
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @.Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make

END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...

...............

This stored procedure work very well.

The question is how I add some dynamic condition inside the where condition.

I want to add:

If @.Model <> "See All Models"
AND a.Model = @.Model'

If @.AutoType <> "New/Used"
AND a.Condition = @.AutoType

I try several ways, but fail.

If you know how to add these two dynamic parameters into the condition of stored procedure, please help.Pass paremeters like this:

CREATE Procedure myAutoSearch
(
@.Make varchar(50),
@.Model varchar(50) = NULL,
@.AutoType varchar(50),
@.Miles float,
@.Zipcode varchar(5)
)

and then do this in the Where clause:

a.Model = IsNull(@.Model,a.Model)

IsNull returns the left-most non-null value, so if @.Model is not passed in (and thus, gets a default value of NULL) then the comparison will be a.Model=a.Model, and thus always true.|||Hi douglas,

Thanks for answering my question. I think you misunderstand my question.
There are a lot of models for each Make. So if user select specific Make (like "Toyata") and "See All Model", then I only need add one condition "AND a.Make = @.Make" in my SP. But if user select specific Make and specific Model (like Tayata, Corolla), then in my where condition, I need add two condition " AND a.Make = @.Make AND a.Model = @.Model". So I need dynamic to add "AND a.Model = @.Model".

In your code, if I don't pass Model (Model=' ' or 'NULL') or Pass Model = "See All Model", then Model condition will become "AND a.Model = 'NULL' ", not any model will be retrieved.

In my situation, I have two or three this kind of dynamic conditions.

Any way to solve this problem?

Thanks again.

Lin|||The code I supplied will handle EXACTLY that situation. If parameter is NULL, then ALL will be selected. As I explained:

IsNull returns the left-most non-null value, so if @.Model is not passed in (and thus, gets a default value of NULL) then the comparison will be a.Model=a.Model, and thus always true.|||A small suggestion for the Original Poster is to d/l or use Books Online that's available for free download at Microsoft.com/Sql or in the Start Menu under Sql Server.

It's a great help doc that I have in my task bar as a quick link, and I use it frequently.

that's all. :) Enjoy your day.|||Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

Hi douglas,

Here is my SP, I have added @.model = NULL, and added IsNull in where condition as you said:

..............

CREATE Procedure Ruying_AutoSearch7
(
@.Make varchar(50),
@.Model varchar(50) = NULL,
@.Miles float,
@.Zipcode varchar(5)
)
AS
DECLARE @.RowCount int
SELECT @.RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @.Zipcode AND CityType = 'D'
if @.RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@.ZIPCode,@.Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @.Make
AND a.Model = IsNull(@.Model,a.Model)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @.Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO

...............

The following is my middle tier class funtion:

..................

Public Function GetAutoSearchItems7(ByVal Make As String, ByVal Model As String, ByVal Miles As Double, ByVal Zipcode As String) As SqlDataReader
 ' Create Instance of Connection and Command Object
 Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim myCommand As SqlCommand = New SqlCommand("Ruying_AutoSearch6", myConnection)
 ' Mark the Command as a SPROC
 myCommand.CommandType = CommandType.StoredProcedure
 ' Add Parameters to SPROC
 Dim parameterMake As SqlParameter = New SqlParameter("@.Make", SqlDbType.VarChar, 50)
 parameterMake.Value = Make
 myCommand.Parameters.Add(parameterMake)
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
 Dim parameterMiles As SqlParameter = New SqlParameter("@.Miles", SqlDbType.Float, 8)
 parameterMiles.Value = Miles
 myCommand.Parameters.Add(parameterMiles)
 Dim parameterZipcode As SqlParameter = New SqlParameter("@.Zipcode", SqlDbType.VarChar, 5)
 parameterZipcode.Value = Zipcode
 myCommand.Parameters.Add(parameterZipcode)
 ' Execute the command
 myConnection.Open()
 Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
 ' Return the datareader result
 Return result
 End Function

.............

The folowing is ASP.NET code behind code:

....................

 SMake = Request.Form("ddlMake")
 SModel = Request.Form("ddlModel")
 lblMiles.Text = Request.Form("ddlMile")
 SMile = CInt(lblMiles.Text)
 lblZipcode.Text = Request.Form("txtZipcode")
 AutoCondition = Request.Form("rdlAutoCondition")
 lblMake.Text = SMake
 If SModel = "See All Models" Then
 lblModel.Text = ""
 Else
 lblModel.Text = SModel
 End If
 If AutoCondition = "New" Then
 lblAutoCondition.Text = "New"
 ElseIf AutoCondition = "Used" Then
 lblAutoCondition.Text = "Used"
 Else
 lblAutoCondition.Text = "New/Used"
 End If
 Dim mySearch As Ruying.SearchDB = New Ruying.SearchDB()
 dgSearchResult.DataSource = mySearch.GetAutoSearchItems7(SMake, lblModel.Text, SMile, lblZipcode.Text)
 dgSearchResult.DataBind()
 dgSearchResult.Dispose()

...............

The resule is that if I select "See All Model", nothing show up.
I change the code to:

 If SModel = "See All Models" Then
 lblModel.Text = "NULL"
 Else
 lblModel.Text = SModel
 End If

The result is same.

But if I select other model (specific model), there are data show up.

Anywhere I can change?

Thanks.

Lin|||You are confusing NULL with 'NULL'. NULL (no quotes) is a special word in SQL Server meaning no value, whereas 'NULL' is a 4 character string.

Do this:

If Model<>String.Empty AND Model<>"See All Models" then
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
End If

This way, the parameter is NOT added if not set, and the default set will be NULL (as opposed to 'NULL', 'Show All Models' or '').|||Hi douglas,

I use

If Model<>String.Empty then
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
End If

in the middle tier function. Now SP works very well. Thank you very much.

One more question in my program.

In asp page, I have AutoCondition to be selected. It has three values as "New", "Used" and "New/Used". In database, AutoCondition has "New", "Like New", "Good" and "Acceptable" values. Now I must use two stored procedure to retrieve the data. I use: AND a.Condition = IsNull(@.Condition, a.Condition) for user select "New" and "New/Used" (when select the "New/Used", set @.Condition = NULL, just like above case). I use: AND a.Condtion <> 'New' in second stored procedure for user select "Used".

Does any way can dynamic change this condition in WHERE clause and become just one stored procedure?

Thanks.

Lin|||I would create a table with the car conditions.

ConditionID int IDENTITY
Description nvarchar(100)
IsNew bit

Then, don't store the text, but store the COnditionID in the Auto table. Only "New" qualifies as new. So you would do a join, and have a condition like:

IsNew=IsNull(@.IsNew,IsNew)

Pass 1 for @.IsNew if they select New, 0 if they select Used, and NULL if they say New/Used.

Opon reflection, it is possible bit cannot be null, in which case you would need to use SmallInt (you should check this out).|||I'll try it. Thank you very much.sql