Hello and thank you for taking a moment to read this message. Please forgive me if this question is a bit out of scope. I am writng an application that queries a database. The user can do a search on any of four fields(Order Number, Product Name,ShippedDate, or Ship Name). If the user leaves all fields blank and hits a submit button, I would like the application to return all records from the database. If the user enters appropriate search criteria, I would like the application to return only those records that meet the criteria searched. I am passing a blank string in my aspx code for those fields left blank by the user. (Ex.

If txtOrderNumber.Text Is DBNull.Value

 Then txtOrderNumber.Text = " "

End If

\\* later on in the code assign it to the parameter

Dim objCommand As New Data.SqlClient.SqlCommand("usp_Find_Orders", objConn)

objCommand.CommandType = Data.CommandType.StoredProcedure


objCommand.Parameters.AddWithValue("@OrderID", txtOrderNumber.Text)

)

My stored procedure is where it becomes confusing. I have seen code that tests for a blank string and sets the parameter equal to '%%' . I think this is what I want to do here but am not 100% sure. I believe my problem is mostly that I don't know how my WHERE clause should look. If anybody has suggestions, I would be most appreciative. Stored Procedure is below. Thank You- Jason

CREATE PROCEDURE [dbo].[usp_Find_Orders]
(


@OrderID varchar(50),
@ProductName varchar (100),
@ShippedDate varchar (100),
@ShipName varchar (100)
)

AS
 If @OrderID=" "
     SET @OrderID="%%"

If @ProductName=" "
     SET @ProductName="%%"

If @ShippedDate=" "
     SET @ShippedDate="%%"

If @ShipName=" "
     SET @ShipName="%%"

SELECT [Order Details].Quantity, Orders.OrderID, Orders.OrderDate, Products.ProductName, Orders.ShippedDate, Orders.ShipVia, Orders.ShipName,
                      Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode
FROM [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
                      Products ON [Order Details].ProductID = Products.ProductID

Where
Orders.OrderID Like @OrderID
AND
ProductName Like '%' + @ProductName + '%'
AND
ShippedDate Like '%' + @ShippedDate +'%'
AND
ShipName Like '%'+ @ShipName +'%'
GO

First "s should be 's
Second, any date columns should be datatime

Ex:
CREATE PROCEDURE [dbo].[usp_Find_Orders]
(


@OrderID varchar(50),
@ProductName varchar (100),
@ShippedDate varchar (100),
@ShipName varchar (100)
)

AS

declare @OrderID_flg int
declare @ProductName_flg int
declare @ShippedDate_flg int
declare @ShipName_flg int


SET @OrderID_flg = 1
SET @ProductName_flg = 1
SET @ShippedDate_flg = 1
SET @ShipName_flg = 1



 If @OrderID=''
     SET @OrderID_flg= 0

If @ProductName=''
     SET @ProductName_flg= 0

If @ShippedDate=''
     SET @ShippedDate_flg= 0

If @ShipName=''
     SET @ShipName_flg= 0

SELECT [Order Details].Quantity, Orders.OrderID, Orders.OrderDate, Products.ProductName, Orders.ShippedDate, Orders.ShipVia, Orders.ShipName,
                      Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode
FROM [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
                      Products ON [Order Details].ProductID = Products.ProductID

Where
   ( ( @OrderID_flg = 1 AND Orders.OrderID = @OrderID ) OR ( @OrderID_flg = 0 ) ) AND
   ...Repeat for each variable


GO

Jim

Thank you very much. That helped a lot.- Jason

Great.. glad to help...
:)

Jim