Solved

variable not being used correctly

Posted on 2014-01-15
9
169 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 33

Expert Comment

by:paulmacd
ID: 39783843
What are the odds @searchString contains single or double quote marks?
0
 
LVL 142

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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 142

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now