Script error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Script error

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-17 : 16:20:58

Can someone help me, I have a script that is not working properly.


DECLARE @submitter	NVARCHAR(50)
DECLARE @DaysBack	INT
DECLARE	@onrows 	NVARCHAR(100)
DECLARE @selcols 	NVARCHAR(100)
DECLARE @sql		VARCHAR(4000)

SET @selcols 		= 'slow laptop'
SET @onrows 		= REPLACE(@selcols, ' ', '%'' or work_log like ''%')
SET @submitter 		= 'Appraisal Helpdesk'
SET @DaysBack  		= -90

	SELECT 
		case_id_ [ticket number], 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, '1/1/1970 00:00:00'), 101) [create date],
		SUBSTRING(work_log, 20, 4000) work_log
	FROM
        	hpd_helpdesk
	WHERE 
		work_log like '%' + @onrows + '%' and Submitted_By_Helpdesk = @submitter and 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, '1/1/1970 00:00:00'), 101) < DATEADD(dd, @DaysBack,  GETDATE())

it does not return any rows, but when i change the @selcols to just 'slow' then it returns 41 rows.

I even printed out the statement and it looks fine


DECLARE @submitter	NVARCHAR(50)
DECLARE @DaysBack	INT
DECLARE	@onrows 	NVARCHAR(100)
DECLARE @selcols 	NVARCHAR(100)
DECLARE @sql		VARCHAR(4000)

SET @selcols 		= 'slow laptop'
SET @onrows 		= REPLACE(@selcols, ' ', '%'' or work_log like ''%')
SET @submitter 		= 'Appraisal Helpdesk'
SET @DaysBack  		= -90

set @sql = '
	SELECT 
		case_id_ [ticket number], 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, ''1/1/1970 00:00:00''), 101) [create date],
		SUBSTRING(work_log, 20, 4000) work_log
	FROM
        	hpd_helpdesk
	WHERE 
		work_log like ''%' + @onrows + '%'' and Submitted_By_Helpdesk = @submitter and 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, ''1/1/1970 00:00:00''), 101) < DATEADD(dd, @DaysBack,  GETDATE())'

print @sql

and it shows


	SELECT 
		case_id_ [ticket number], 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, '1/1/1970 00:00:00'), 101) [create date],
		SUBSTRING(work_log, 20, 4000) work_log
	FROM
        	hpd_helpdesk
	WHERE 
		work_log like '%slow%' or work_log like '%laptop%' and Submitted_By_Helpdesk = @submitter and 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, '1/1/1970 00:00:00'), 101) < DATEADD(dd, @DaysBack,  GETDATE())

any ideas on who to get this to work properly?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 20:53:00

See here

select 	 onrows  = REPLACE(selcols, ' ', '%'' or work_log like ''%')
from	
(
	select 'slow' 		as selcols union all
	select 'slow laptop' 	as selcols
) a

result
onrows                                                                                                                                                                                                                                                           
---------------------------------
slow
slow%' or work_log like '%laptop

(2 row(s) affected)

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-17 : 21:09:51

I appriciate your input but i do not understand it.

What I would like to do it be able to pass in a space seperated list of words into the @selcols param and have it search the work_log fo instances of all the words in the @selcols. What i have, works for 1 word but when i put in multiple words it returns 0 records, i think the problem is with the @onrows param since when only 1 word exists in @selcols there is no work to do.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-17 : 21:13:59

Try this.

DECLARE @submitter	NVARCHAR(50)
DECLARE @DaysBack	INT
DECLARE	@onrows 	NVARCHAR(100)
DECLARE @selcols 	NVARCHAR(100)
DECLARE @sql		VARCHAR(4000)

SET @selcols 		= 'slow laptop'
SET @onrows 		= REPLACE(@selcols, ' ', '%'' or work_log like ''%')
SET @submitter 		= 'Appraisal Helpdesk'
SET @DaysBack  		= -90

	SELECT 
		case_id_ [ticket number], 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, '1/1/1970 00:00:00'), 101) [create date],
		SUBSTRING(work_log, 20, 4000) work_log
	FROM
        	hpd_helpdesk
	WHERE 
		(work_log like '%' + @onrows + '%') and Submitted_By_Helpdesk = @submitter and 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, '1/1/1970 00:00:00'), 101) < DATEADD(dd, @DaysBack,  GETDATE())

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-20 : 10:05:57

nope, that didnt work either, I know it is something that simple that I am missing i just cannot figure out what it is.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 19:04:02

Post the table DDL and some sample data.

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-21 : 10:15:47

[code]
Here is the table

create table HPD_HelpDesk
(
[Ticket Number] VARCHAR(10) IDENTITY NOT NULL,
Create_Date VARCHAR(10) NOT NULL,
Work_Log Text NOT NULL
)

Here is some sample data

insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00672342', '01/03/2005', 'this is a laptop')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00672434', '01/03/2005', 'the laptop is slow')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00672671', '01/03/2005', 'i walk slow')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00672886', '01/03/2005', 'i dropped a laptop')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00672906', '01/03/2005', 'this should not match')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00672931', '01/03/2005', 'the slow laptop caught fire')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00674070', '01/04/2005', 'drive slow in school zones')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00674090', '01/04/2005', 'someone stole my laptop')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00674338', '01/04/2005', 'this has nothing to do')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00674426', '01/04/2005', 'i am sleepy')
insert into hpd_helpdesk([ticket number], create_date, work_log) values ('HD00674538', '01/04/2005', 'the quick brown fox ate my laptop slowly')

[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-03-21 : 11:14:55

If you fix your DDL and execute it, are you able to select anything from it using your query?

They are both so far off I'm not quite sure how to offer help. Perhaps, if the DDL was fixed up to allow your first example with just the 'slow' to return data, then we could be of more help.

It appears to me that you are mixing SQL and Dynamic SQL which is not going to work.

-Lamprey

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-21 : 12:24:45

lamprey, you are correct, sorry about that.

Here is the ddl
create table HPD_HelpDesk
(
[Ticket Number] VARCHAR(10) NOT NULL,
Create_Date VARCHAR(10) NOT NULL,
Work_Log Text NOT NULL
)

and a new sample of the select i am trying to execute

DECLARE @DaysBack INT
DECLARE @onrows NVARCHAR(100)
DECLARE @selcols NVARCHAR(100)

SET @selcols = 'slow laptop'
SET @onrows = REPLACE(@selcols, ' ', '%'' or work_log like ''%')

SELECT
[ticket number], Create_Date, work_log
FROM
hpd_helpdesk
WHERE
work_log like '%' + @onrows + '%'

this will return nothing as it is right now, if i change @selcols to just slow or just laptop it works fine

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-03-21 : 12:50:14

quote:
Originally posted by HalaszJ
WHERE
work_log like '%' + @onrows + '%'

Yep, that won't work the way you think it should. Then you only have one value it makes sense:


WHERE
    work_log like '%slow%'

But when you have multiple values you get something like:


WHERE
    work_log like '%slow% and worklog like %laptop%'

Which, obviously, not what you are trying to acomplish. I realize the quoting is off, but you get the idea. I'd have to sit down and mess with this for a while to get a decent solution, but The simple solution is to execute this as dynamic sql similar to what you generated in your fist sample to verify what was being produced.

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-21 : 13:45:21

actually

WHERE
work_log like '%slow% and worklog like %laptop%'

is what i want, i want to search the worklogs for any instance of slow or laptop or, slow and laptop, any other amount of items.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-03-22 : 04:05:17

quote:
Originally posted by HalaszJ

actually

WHERE
work_log like '%slow% and worklog like %laptop%'

is what i want, i want to search the worklogs for any instance of slow or laptop or, slow and laptop, any other amount of items.

It appears to me there is a little confusion about how LIKE works. Please correct me if I am wrong, but what I think you want is:


WHERE
    work_log LIKE '%slow%'
    OR work_log LIKE '%laptop%'

The above WHERE clause would meet your criteria stated above.

There are ways to do this without dynamic SQL. However, without knowing all the details, dynamic SQL is probably the simplest option and, possibly, the best performing. Here is a sample that you can try it against your real data since the DDL and data provided doesn’t match the query.

DECLARE @submitter	NVARCHAR(50)
DECLARE @DaysBack	INT
DECLARE	@onrows 	NVARCHAR(100)
DECLARE @selcols 	NVARCHAR(100)
DECLARE @sql		VARCHAR(4000)

SET @selcols 		= 'slow laptop'
SET @onrows 		= REPLACE(@selcols, ' ', '%'' or work_log like ''%')
SET @submitter 		= 'Appraisal Helpdesk'
SET @DaysBack  		= -90

set @sql = '
	SELECT 
		[ticket number] AS case_id_, 
		CONVERT(NVARCHAR(31), DATEADD(s, create_date, ''1/1/1970 00:00:00''), 101) [create date],
		SUBSTRING(work_log, 20, 4000) work_log
	FROM
        	hpd_helpdesk
	WHERE 
		work_log like ''%' + @onrows + '%'' and Submitted_By_Helpdesk = ''' + @submitter + ''' 
		AND CONVERT(NVARCHAR(31), DATEADD(s, create_date, ''1/1/1970 00:00:00''), 101) < DATEADD(dd, ' + CAST(@DaysBack AS VARCHAR) + ',  GETDATE())'

--print @sql
EXEC (@sql)

EDIT: I forgot to mention: if you are dealing with NVARCHAR columns, you may need to tweak the dynamic sql a bit as there are some slight differences when dealing with UNICODE strings and LIKE. You can investigate this using Books Online (Transact-SQL Help).

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-03-24 : 14:58:26

Lamprey, thanks so much, that is exactly what i needed.

I knew i was close, i just had some 's out of place :)

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources