Improve company productivity with a Business Account.Sign Up

x
?
Solved

variable not being used correctly

Posted on 2014-01-15
9
Medium Priority
?
183 Views
Last Modified: 2014-01-16
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

0
Comment
Question by:portlight
9 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39783836
When you are printing the variable, does it include the single quotes? You would need them to use it in the LIKE criteria.
0
 
LVL 35

Expert Comment

by:Paul MacDonald
ID: 39783843
What are the odds @searchString contains single or double quote marks?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39783895
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:portlight
ID: 39784165
@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.....
0
 

Author Comment

by:portlight
ID: 39784262
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.
0
 

Author Comment

by:portlight
ID: 39784269
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

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 39784539
I see that the problem is exactly what I described: you are passing the quotes into the string. Remove them. Also you have a trailing space after those quotes which you need to remove...
0
 

Author Comment

by:portlight
ID: 39785358
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 %
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39785482
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

0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question