Sproc not Working with WildCard

[email protected] · July 14th, 2005, 04:23 PM

Hello Everyone and thanks for your help in advance. I am writing a sproc for a SQL Server 2000 databse and am running into problems. The sproc is effectively a dictionary search looking for wildcard matches. When I write the SQL statement in Query Analyzer, it works fine:

SELECT * FROM tblDiagnosis
WHERE (Diagnosis LIKE '%')

returns all records. However the sproc:

CREATE PROCEDURE [sp_Search]
(@Diagnosis [char](100))
AS
SELECT * From tblDiagnosis
WHERE
([Diagnosis] Like @Diagnosis)
ORDER BY [Diagnosis]
GO

Fails to return any records. However, when I replace the @Diagnosis with '%', it returns all records. Obviously, I want to pass the Diagnosis parameter dynamically so I can perform searches, however, I really don't know why this isn't working. Any help on this issue will be greatly appreciated. Thanks.

rgerald · July 14th, 2005, 04:50 PM

A simple syntax error:

Change as follows

Code:

CREATE PROCEDURE [sp_Search]
(@Diagnosis             [char](100))
AS 
SELECT * From tblDiagnosis 
WHERE 
([Diagnosis]         Like ''' + @Diagnosis + ''')
ORDER BY  [Diagnosis]

Not the three single quotes on each side of @Diagnosis.

Rand

rgerald · July 14th, 2005, 04:51 PM

Not should be Note

Rand

rgerald · July 14th, 2005, 05:32 PM

Oops! I goofed! Try this:

Code:

CREATE PROCEDURE [dbo].[sp_Search]
(@Parameter [varchar](100))
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT * FROM tblDiagnosis WHERE Diagnosis LIKE (''' + LTRIM(RTRIM(@Parameter)) + ''') ORDER BY Diagnosis'
EXEC(@SQL)
GO

Rand

[email protected] · July 14th, 2005, 08:08 PM

Thanks for the help. However, I copied the code directly into the sproc and it didn't work. Still returns nothing. Any ideas?

jbenson001 · July 14th, 2005, 11:28 PM

print out the @sql variable before you exectue it and make sure it is properly formatted. If it looks OK, try running that string in QA to see if you get any results.

pgtips · July 15th, 2005, 04:40 AM

Hi Hugh,

Your problem is here:
(@Diagnosis [char](100))

when you pass in '%' as the parameter, then because you set it to a fixed width CHAR, what you are actually passing in is '%+99spaces'. That's why you don't get any matches. Just change the datatype to a varchar and you'll be fine.

rgds
Phil

[email protected] · July 15th, 2005, 05:38 AM

Thanks Phil. That did the trick.