Store procedure to search a company table

[RaulMA]

I need help to create
an store procedure that search a Company table passing 4 parameters.One of the requirements is to allow the user to combine those parameters.

Parameters: Region_Name, Type (Client, Dealer), Status (Active, Inactive, Prospect), Name

The company table has a foreing key from Regions table, this key need to be used to get Region_Name

Any help will be appreciated.

 

[bborissov]

CREATE PROCEDURE SearchCompany
@RegionName varchar(200),
@Type varchar(6),
@Status varchar(8),
@Name varchar(200)
AS
 BEGIN
     SELECT .....
          FROM CompanyTable
     LEFT JOIN RegionTable
          ON CompanyTable.RegionFK = RegionTable.PK
     WHERE (@RegionName IS NULL OR RegionTable.RegionName = @RegionName) AND
           (@Type       IS NULL OR CompanyTable.Type      = @Type)       AND
           (@Status     IS NULL OR CompanyTable.Status    = @Status)     AND
           (@Name       IS NULL OR CompanyTable.Name      = @Name)
 END

(not tested)

END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[RaulMA]

Thanks for the help, but something wrong happens when I run the store procedure with one of the parameters empty.It should execute the store procedure just with the parameters I passed with out required all of them to be submitted.

EX
SearchCompany 'us','Dealer','Active',''

Returns 0 records


ALTER PROCEDURE SearchCompany
@Region_Name varchar(40),
@Type varchar(20),
@Client_Active varchar(30),
@Short_Name varchar(30)
AS
BEGIN
SELECT company_Name,Short_Name_1 FROM Company
LEFT JOIN Region ON Company.Region_Id = Region.Region_Id
WHERE
(@Region_Name IS NULL OR Region.Region_Name = @Region_Name) AND
(@Type IS NULL OR Company.Type = @Type) AND
(@Client_Active IS NULL OR Company.Client_Active = @Client_Active) AND
(@Short_Name_1 IS NULL OR Company.Short_Name_1 = @Short_Name)
END

 

[gmmastros]

That's because you're not sending NULL in for the parameter, you're sending an empty string (which is not the same). To accomodate the empty string...

ALTER PROCEDURE SearchCompany
@Region_Name varchar(40),
@Type varchar(20),
@Client_Active varchar(30),
@Short_Name varchar(30)
AS
BEGIN
   SELECT company_Name,Short_Name_1 FROM Company
   LEFT JOIN Region ON Company.Region_Id = Region.Region_Id
   WHERE 
   (IsNull(@Region_Name, '') = '' OR Region.Region_Name = @Region_Name)                     AND
   (IsNull(@Type, '') = '' OR Company.Type       = @Type)                            AND
   (IsNull(@Client_Active, '') = '' OR Company.Client_Active = @Client_Active)  AND
   (IsNull(@Short_Name_1, '') = '' OR Company.Short_Name_1    = @Short_Name)
END

With this, the parameter can be NULL or an empty string and the results would be the same.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom