Create/Using Stored Procedures WITH Wildcards

[PilotHaz]

I have currently looked through a couple of the posts on here about Stored Procedures but using this information did not work. Here is what I have done. I am trying to use wild cards for I want to search through my database for specific Programs that have been added to the database from the computer's login script.

In my search I want to call a stored procedure to make it easier then making a call each time (and just to use the one that is already created.)

Here is the sp code:

CREATE PROCEDURE [dbo].[sp_SrchProgramsByProgramName]
@program char(100) 
AS
SELECT * FROM Programs
WHERE ProgramName LIKE '%' + @program + '%'
GO
 

This is the call I was trying to use in SQL Query Analyzer for it:

execute dbo.sp_SrchProgramsByProgramName 'Adobe'
 

Yet, it is returning nothing for the results. If I do the exact names I know the results will show up but I want to have it so if we are looking up just Adobe Products or just AutoCAD, if there is other things in the programName string then it won't matter.

Please if you would be able to help me it would be greatly appreciated.

[ilearneditonline]

You don't have to worry with concat on the where clause.

CREATE PROCEDURE [dbo].[sp_SrchProgramsByProgramName]
(
    @program char(100)
)
    AS
    SELECT * FROM Programs
    WHERE ProgramName LIKE '%@program%'

[PilotHaz]

You don't have to worry with concat on the where clause.

CREATE PROCEDURE [dbo].[sp_SrchProgramsByProgramName]
(
    @program char(100)
)
    AS
    SELECT * FROM Programs
    WHERE ProgramName LIKE '%@program%'

That doesn't work. I had tried that before as well, it comes back with 0 rows affected.

[ilearneditonline]

That doesn't work. I had tried that before as well, it comes back with 0 rows affected.

This does work, I have it working here. Don't use a char for your parameter, that has additional whitespace and I have not been able to get it to work. Declare a varchar variable to hold the criteria with the `%` already added.

 CREATE PROCEDURE [dbo].[sp_SrchProgramsByProgramName] 
(
@program varchar(100)
)
AS
DECLARE @criteria varchar(102)
SET @criteria = '%' + @program + '%' 
SELECT * FROM Programs
WHERE ProgramName LIKE @criteria
 

[PilotHaz]

This does work, I have it working here. Don't use a char for your parameter, that has additional whitespace and I have not been able to get it to work. Declare a varchar variable to hold the criteria with the `%` already added.

 CREATE PROCEDURE [dbo].[sp_SrchProgramsByProgramName] 
(
@program varchar(100)
)
AS
DECLARE @criteria varchar(102)
SET @criteria = '%' + @program + '%' 
SELECT * FROM Programs
WHERE ProgramName LIKE @criteria
 

Thank you very much. I see that it was the actual char I believe like you said. Varchar works fine with it. Thanks again.