Stored Procedure and the Like statement

Hi there,

I'm using and SQL2005 server and asp 2.

I am trying to filter / search for records using a stored procedure, based on one or more search criteria
supplied by a user.

The asp code below creates a whereclause that is used to call the stored procedure.

Where a value has not been searched on or selected the value of that field is NULL.

If a customers types "a" then I want to return all the customers starting with the letter "a".
A further selection might then request all customers from a town beginning with "t" etc etc.
You get the picture.

session("Cust_WC") = "@CustomerName='a', "
session("Cust_WC") = session("Cust_WC") & "@CustomerAdd1=NULL, "
session("Cust_WC") = session("Cust_WC") & "@CustomerTown=NULL, "
session("Cust_WC") = session("Cust_WC") & "@CustomerCounty=NULL, "
session("Cust_WC") = session("Cust_WC") & "@CustomerPostCode=NULL, "
session("Cust_WC") = session("Cust_WC") & "@CustomerCountry=NULL, "
session("Cust_WC") = session("Cust_WC") & "@CustomerType=NULL, "
session("Cust_WC") = session("Cust_WC") & "@CustomerAccount=NULL "

' Debug Code
response.write session("Cust_WC") + "<br>"

' Open a connection to the database
Set db = Server.CreateObject("ADODB.Connection")
db.Open "driver={SQL Server};server=xx.xx.xx.xx;database=xx","xx","xx"

' Count all the records based on the user listbox selection
set rs = db.execute("EXEC Customer_Count " & session("Cust_WC"))
MyTotalRecs = rs("CountofCustomerID")
rs.close
set rs = nothing
set db = nothing

' Debug Code
response.write "Total Records Returned = " &  MyTotalRecs


The stored procedure I am using will only work if I supply the whole customer name to the stored procedure call.

ALTER PROCEDURE [dbo].[Customer_Count]

@CustomerName nchar(100),
@CustomerAdd1 nchar(100),
@CustomerTown nchar(30),
@CustomerCounty nchar(30),
@CustomerPostCode nchar(10),
@CustomerCountry nchar(15),
@CustomerType nchar(30),
@CustomerAccount nchar(20)

AS

BEGIN
    SELECT Count(CustomerID) AS CountOfCustomerID

    FROM Customers

    WHERE (@CustomerName IS NULL OR CustomerName like @CustomerName + '%')
    AND ( @CustomerAdd1 IS NULL OR CustomerAdd1 = @CustomerAdd1)
    AND ( @CustomerTown IS NULL OR CustomerTown = @CustomerTown)
    AND ( @CustomerCounty IS NULL OR CustomerCounty = @CustomerCounty)
    AND ( @CustomerPostCode IS NULL OR CustomerPostCode = @CustomerPostCode)
    AND ( @CustomerCountry IS NULL OR CustomerCountry = @CustomerCountry)
    AND ( @CustomerType IS NULL OR CustomerType = @CustomerType)
    AND ( @CustomerAccount IS NULL OR CustomerAccount = @CustomerAccount)

END

Please can you advise where I am going wrong or if there is another way of working this idea.

Regards
PaulStart Free Trial