asked on

variable not being used correctly

I have a stored procedure that is using a variable
When I print the variable - it prints as being parsed correctly - when I substitute what is printed for the variable in the stored procedure it is pulling the data expected but when i try to use the variable it brings forth no data.
The variable in question is @searchString
Line 28 is the line in question

Stored Procedure

CREATE PROCEDURE [dbo].[Office_stats]
     @searchString varchar( 38 )

as

IF @searchString IS NULL SET @searchString = '%'
IF @searchString = 'LIKE ''%'' ' SET @searchString = '%'
IF @searchString IS NOT NULL SET @searchString =  SUBSTRING (@searchString , CHARINDEX ( ' ', @searchString ) + 1, LEN (@searchString ))

SELECT * 

from employee
left outer join OfficeEmps on employeeid = acempid and ( acchannelstatus = 'complete' or acchannelstatus = 'WaitForComplete')
left outer join EmpsHistory on chempid = employeeid and chid is not null and chdistrict = ''
left outer join officeNames history on chid= history .cnuid and chid is not null and chdistrict = ''
where worklocation in(
'Blue Room Services' ,
'Green States town' ,
'Estats service' ,
'Valley union' ,
'pringles Office' ,
'Eddies Office' ,
'Bluntsville Center' ,
'Palisdale Center' ,
'Union Station Center' 
)
and deptid in( '523' ,'521' , '525', '529' ,'524' , '631')
and worklocation LIKE @searchString
 Go
PRINT @searchString
Go

Open in new window

Jerry Miller

When you are printing the variable, does it include the single quotes? You would need them to use it in the LIKE criteria.

Paul MacDonald

What are the odds @searchString contains single or double quote marks?

Guy Hengel [angelIII / a3]

I would also presume that your @searchString actually contains quotes, which should not.

if your "final" query normally would read:

and worklocation LIKE '%some_value%'

your variable needs to have the value like this:

set @searchString = '%some_value%'

and not with the quotes:
set @searchString = '''%some_value%'''

hope this clarifies.

portlight

ASKER

@jmiller1979 yes it contains the quotes that are needed to use with the LIKE criteria

@paulmacd the variable prints out exactly like it needs to i.e. ('LIKE ''gree%'' ' prints out as 'gree%') and when that is replaced
(worklocation LIKE 'gree%') then all rows with worklocation = 'Green States town' is returned....but NOT using the @searchString variable

@Guy Hengel the subString leaves the variable passed with one set of single quotes as it needs to

For some reason it just is NOT working I can substitute the @searchString with what is actually passed and the query works perfect - it is almost like in the actual query @searchString is not registering.....

portlight

ASKER

UPDATE
exec CC_Report_OverTimeListWeekDayTest 'LIKE ''Gree%'' '
exec CC_Report_OverTimeListWeekDayTest null
exec CC_Report_OverTimeListWeekDayTest 'LIKE ''%'' '

Prints out the following values for @searchString
'Gree%'
%
%

SO it looks like I need to change my
SUBSTRING (@searchString , CHARINDEX ( ' ', @searchString ) + 1, LEN (@searchString ))

to print
Gree%

as opposed to 'Gree%'

But I am having trouble getting it to NOT print the quotes.

portlight

ASKER

This worked for exec CC_Report_OverTimeListWeekDayTest 'LIKE ''Ash%'' '

SUBSTRING(@searchString, 7, CHARINDEX(' ', @searchString) - 1)

As it sets @searchString to Ash%

BUT it does NOT work for the following

exec CC_Report_OverTimeListWeekDayTest 'LIKE ''BLUE%'' '
set it to Blue and I need to make sure that the % is included regardless of the length between the '' and the %

exec CC_Report_OverTimeListWeekDayTest null
set it to % and returns all rows

exec CC_Report_OverTimeListWeekDayTest 'LIKE ''%'' '
sets it to %' which returns no rows

I am really not understanding how this is happening :

Here is the set codes

IF @searchString IS NOT NULL SET @searchString =  SUBSTRING(@searchString, 7, CHARINDEX(' ', @searchString) - 1)
IF @searchString IS NULL SET @searchString = '%'
IF @searchString = 'LIKE ''%'' ' SET @searchString = '%'

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

portlight

ASKER

I can not control what is being passed - it is specific to the application
How can I remove them using the substring function and still make sure that enough letters are sent to include the %

Guy Hengel [angelIII / a3]

this should do:

IF @searchString IS NULL SET @searchString = '%'
-- this next one is not needed, actually:
--IF @searchString = 'LIKE ''%'' ' SET @searchString = '%'

IF @searchString LIKE 'LIKE ''%'' ' SET @searchString =  SUBSTRING(@searchString, 7, LEN(@searchString) - 8)

Open in new window