Hi all,
I am new to Visual Studio 2005 and am having a problem with calling a Sql Server Stored Procedure. For now, I have created a page with a search button, two text boxes that will hold my input values into the Stored proc, and a datagrid that will hold my output. When I try to send in just one of the text box values, I get nothing back. If I send in both, I get the correct return. I have tested this proc in sql server and it works fine with one or both values as input (using exec command). I have even changed the default value for the StreetName text box to be a valid street name (searching for street name and employee id from my database) to see what I get and I get the correct return data (as if I sent in both values). Why can't I just send in one value and get the return for it? My proc select statement is very simple:

ALTER PROCEDURE [Sam_Search_Pinewood]
    -- Add the parameters for the stored procedure here
@StreetName As varchar(30) = null,
@AssignedEIN As varchar(4) = null


AS
Begin

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

 Select CaseNumber
from fir_fieldinvreport
where (@StreetName is NULL or @StreetName = StreetName)
and (@AssignedEIN is NULL or @AssignedEIN = AssignedEIN)

end

Thanks!

Since the stored proc has two variables defined, it needs two passed. In the case of only one input box being entered, are you passing an empty string in the other missing variable? Try passing an empty string in teh case of a blank input box and changing your WHERE statement to the following:

WHERE (CASE WHEN @StreetName <> '' THEN @StreetName ELSE StreetName END = StreetName) AND (CASE WHEN @AssignedEIN <> '' THEN @AssignedEIN ELSE AssignedEIN END = AssignedEIN)

I hope this helps.


-Orcabelle

Hi Orcabelle. Thanks for the advice. I tried that and it worked!!