Hello,
You can write the stored procedure as shown below
CREATE PROCEDURE spcustomer
@ContactName NVARCHAR(30) = NULL,
@ContactTitle NVARCHAR(40) = NULL,
@Address NVARCHAR(80) = NULL,
@City NVARCHAR(30) = NULL
AS
SELECT *
FROM customers c
WHERE (c.ContactName = @ContactName OR @ContactName IS NULL)
AND (c.ContactTitle = @ContactTitle OR @ContactTitle IS NULL)
AND (c.Address = @Address OR @Address IS NULL)
AND (c.City = @City OR @City IS NULL);
NotePlease not that passing and empty string to this procedure may not return rows as empty string is not same as NULL
value.
Here is a code snippet depicting one of the possible way for calling the report
public static CrystalReport1 getcutomer(string ContactName, string strTitle, string strAddress, string strCity)
{
string[] arrParams = new string[4];
arrParams[0] = (String.IsNullOrEmpty(ContactName) ? null : ContactName);
arrParams[1] = (String.IsNullOrEmpty(strTitle) ? null : strTitle);
arrParams[2] = (String.IsNullOrEmpty(address) ? null : strAddress);
arrParams[3] = (String.IsNullOrEmpty(strCity) ? null : strCity);
Database db = DatabaseFactory.CreateDatabase("myconn");
DataSet ds = db.ExecuteDataSet("spcustomer", arrParams);
ds.Tables[0].TableName = "Customers";
CrystalReport1 report = new CrystalReport1();
report.SetDataSource(ds);
return report;
}