creating stored procedure with optional parameters?

[Ovatvvon]

Hello all,

I have a current stored procedure that I believe I need to change so that one of the parameters are optional. It currently takes inputs from a webform: company, client id, phone, and status, etc.

There are multiple companies to choose from, and so we want to enable the possibility of searching all companies, not just one specific one. So, I've created a select box / dropdown box menu option to search in all the companies. When I did that though, the report no longer works because the stored procedure is expecting that company input, not a Null value. I tried changing the stored procedure to check for a null value like so:

CREATE     PROCEDURE [dbo].[sql_searchClientPhone] 
	@company varchar(10),
	@id varchar(7),
	@name varchar(50),
	@status varchar(1),
	@phone varchar(16)
AS
IF @company = Null
		SELECT dba_name, id FROM client 
		WHERE id LIKE @id AND dba_name LIKE @name AND phone LIKE @phone AND client_status = @status
		ORDER BY dba_name
ELSE
		SELECT dba_name, id FROM client 
		WHERE id LIKE @id AND company = @company AND dba_name LIKE @name AND phone LIKE @phone AND client_status = @status
		ORDER BY dba_name
GO

Now the page gives the following error:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Cannot use empty object or column names. Use a single space if necessary.

Can anyone tell me how to make the company paramter an optional one...Or what I should do in a situation like this?

-Ovatvvon :-Q

 

[gmmastros]

Try adding SET NOCOUNT ON to your stored procedure.

CREATE     PROCEDURE [dbo].[sql_searchClientPhone] 
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16)
AS
[red]SET NOCOUNT ON[/red]
IF @company = Null
        SELECT dba_name, id FROM client 
        WHERE id LIKE @id AND dba_name LIKE @name AND phone LIKE @phone AND client_status = @status
        ORDER BY dba_name
ELSE
        SELECT dba_name, id FROM client 
        WHERE id LIKE @id AND company = @company AND dba_name LIKE @name AND phone LIKE @phone AND client_status = @status
        ORDER BY dba_name

-George

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

 

[Pattycake245]

Why not use case statement instead:

CREATE     PROCEDURE [dbo].[sql_searchClientPhone] 
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16)

AS

SELECT   dba_name, id 
FROM     client 
WHERE    id LIKE @id 
AND      company = case when @company is not null then     
         @company else company end
AND      dba_name LIKE @name 
AND      phone LIKE @phone 
AND      client_status = @status
ORDER BY dba_name

GO

Tim

 

[gmmastros]

I just noticed that the way you are testing for null is not right.

Instead of
If @Company = null

You should use
If @Company Is NULL



-George

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

 

[Ovatvvon]

Hello gmmastros and Pattycake245,

I tried all suggestions from both of you. Unfortunately, in all cases, I still receive the same error.

-Ovatvvon :-Q

 

[gmmastros]

To make an optional parameter, you specify it in the Create Procedure statement, like this...

CREATE     PROCEDURE [dbo].[sql_searchClientPhone] 
    @company varchar(10) = NULL,
    @id varchar(7) = NULL,
    @name varchar(50) = NULL,
    @status varchar(1) = NULL,
    @phone varchar(16) = NULL

-George

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

 

[Pattycake245]

If you are going to check to see if @company is null then when passing it through to the stored proc you must set it to NULL if no values passed.

CREATE     PROCEDURE [dbo].[sql_searchClientPhone] 
    @company varchar(10) = NULL,
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16)

AS

SELECT   dba_name, id 
FROM     client 
WHERE    id LIKE @id 
AND      company = case when @company is not null then     
         @company else company end
AND      dba_name LIKE @name 
AND      phone LIKE @phone 
AND      client_status = @status
ORDER BY dba_name

GO

Or else if an empty string is going to be passed you can use this:

CREATE     PROCEDURE [dbo].[sql_searchClientPhone] 
    @company varchar(10),
    @id varchar(7),
    @name varchar(50),
    @status varchar(1),
    @phone varchar(16)

AS

SELECT   dba_name, id 
FROM     client 
WHERE    id LIKE @id 
AND      company = case when @company <> '' then     
         @company else company end
AND      dba_name LIKE @name 
AND      phone LIKE @phone 
AND      client_status = @status
ORDER BY dba_name

GO

Other than that, is there some code you have ommited for ease of reading?

Tim

 

[Ovatvvon]

Those options also did not work. I did, however, get it to work now. What I did was change the WHERE clause from ...company = @company... to use the LIKE operator, so it now reads: ...company LIKE @company..., and then added a wild-card into the variable in the website backend code.

Thank you all for your help though!!


-Ovatvvon :-Q