?
Solved

syntex error near keyword

Posted on 2011-04-29
15
Medium Priority
?
300 Views
Last Modified: 2012-05-11
IF @searchString IS NULL SET @searchString = LIKE '%'

this is in server 2008 sql - what am i missing please.
0
Comment
Question by:Tagom
  • 6
  • 4
  • 2
  • +3
15 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35493881
You could use something like this:
 
IF @searchString IS NULL 
    SET @searchString = '%'
select * from Customer where Name like @searchString

Open in new window

This would return all the records when @searchString is null and only the records matching searchstring when searchstring has a value.

If this doesn't solve your problem please provide all the code you have so far, and explain what you try to accomplish.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35494009
Post your complete query.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35496187
IF @searchString IS NULL
SET @searchString = '%'
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 8

Expert Comment

by:Ghunaima
ID: 35496307
Try this --  IF @searchString IS NULL SET @searchString = 'LIKE ''%'' '
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35496312
The % sign is enclosed with two ' (single quotes)
0
 
LVL 9

Expert Comment

by:anillucky31
ID: 35496820
From your IF @searchString IS NULL SET @searchString = LIKE'%' it lookes like when null comes you want to query all the records otherwise you want to search according to @searchstring

You can do like this. suppose i have sql table TABLE1 and column name searchstring.

you can write you query like this

select * from TABLE1 where (@searchstring is null or searchstring like @searchstring + '%')

above statement give you same result
as below

IF @searchString IS NULL SET @searchString = '%%'



0
 

Author Comment

by:Tagom
ID: 35505168
I think the issue may be deeper -
I am posting the full query
 intention of query:
Query is to search the two tables based on minStart and maxEnd, must match the uKey
AND if there is a search string entered it should use the passed variable
IF @searchstring is null then it should pass a blank space -
must us the LIKE statement
must work in jasperServer which passes the *searchstring default as "LIKE '%'"

error when executing -
select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as 'Methods',cMinStart,cMaxEnd,
      Active = case cPause when 1 then 'No' when 0 then 'Yes' end  ,csLabel,ctLabel
      from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
      WHERE (((cMinStart <= 'Jan  1 1900 12:00AM' AND cMinStart !> 'Jan  1 2040 12:00AM' AND cMaxEnd > 'Jan  1 1900 12:00AM') AND (cMaxEnd <= 'Jan  1 2040 12:00AM' OR cMaxEnd >= 'Jan  1 1900 12:00AM'))
      OR (cMinStart  >= 'Jan  1 1900 12:00AM' AND cMaxEnd <= 'Jan  1 2040 12:00AM'))
      AND cUtility = '7E7F1614-66EC-46BA-9832-76D98D36452F' AND cLabel = LIKE '%' ORDER BY sDescription, cLabel,Active
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'LIKE'.

1st thing I notice is that the string to figure out the date is not calculating or passing correctly.

USE [acisql2]
GO
/****** Object:  StoredProcedure [dbo].[reportCampaigns]    Script Date: 05/02/2011 09:22:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[reportCampaigns]
	@autoDate int = null,
	@startDate DATETIME = null,
	@endDate DATETIME = null,
	@utilityKey VARCHAR(38),
	@searchString VARCHAR(100)
	
AS
/* Tester:
	exec reportCampaigns null,'1900-01-01 00:00:00', '2011-01-01 00:00:00','7E7F1614-66EC-46BA-9832-76D98D36452F',' LIKE ''%'''
	exec reportCampaigns '6',null, null, '7E7F1614-66EC-46BA-9832-76D98D36452F','LIKE ''%'''

*/

IF LEN(@utilityKey) < 36 
	SET @utilityKey = (SELECT TOP 1 CAST(cUtility AS VARCHAR(38)) FROM campaign)
declare @begin datetime
declare @end datetime
set @begin = cast(@startDate as datetime) 
set @end =cast(@endDate  as datetime)

IF @searchString IS NULL SET @searchString = 'LIKE ''%'' '   

If @autodate is not null
begin
IF @autodate = '0' BEGIN
		SET @begin = DATEADD(yyyy,DATEDIFF(yyyy,0,GETDATE())-20,0)-- all dates
		SET @end = DATEADD(yyyy, DATEDIFF(yyyy,0,getdate())+1, 0)
END		
IF @autodate = '1' BEGIN
	    SET @begin = GETDATE()-- today
		SET @end = DATEADD(dd, DATEDIFF(dd,0,getdate())+1, 0)
END		
IF @autodate = '2'  BEGIN
		SET @begin = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)-- yesterday
		SET @end = GETDATE()
END		
IF @autodate = '3'  BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)-- this week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),+6)
END		
IF @autodate = '4' BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0) -- last week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)
END		
IF @autodate = '5' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE()),0) -- this month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),+30)
END		
IF @autodate = '6' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0) -- last month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),0)
END		
IF @autodate = '7' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)--this year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
END		
IF @autodate = '8' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0) -- last year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
END		
IF @autodate = '9' BEGIN
		SET @begin =  @begin
		SET @end = @end
		
END
end	
IF @startDate IS NULL SET @begin = '1900-01-01 00:00:00'
IF @endDate IS NULL SET @end = '2040-01-01 00:00:00'

DECLARE @start VARCHAR(100)
DECLARE @ends VARCHAR(100)
	
SET @start = CAST(@begin AS VARCHAR(100))
SET @ends = CAST(@end AS VARCHAR(100))


DECLARE @report VARCHAR(4000),@orderby VARCHAR(100)
-- gives correct information for all dates
SET @report ='select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
	Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
	from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
	WHERE (((cMinStart <= '''+ @start +''' AND cMinStart !> '''+ @ends +''' AND cMaxEnd > '''+@start+''') AND (cMaxEnd <= '''+@ends+''' OR cMaxEnd >= '''+@start+'''))
	OR (cMinStart  >= '''+ @start +''' AND cMaxEnd <= '''+@ends+'''))
	AND cUtility = ''' + @utilityKey + ''' AND cLabel = '  + @searchString
	
SET @orderby = ' ORDER BY sDescription, cLabel,Active'

print (@report+@orderby)

exec (@report+@orderby)

Open in new window

0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35505281
Change
AND cLabel = '  + @searchString
to
AND cLabel  '  + @searchString
0
 

Author Comment

by:Tagom
ID: 35506077
@Nicobo - in doing this I get the following error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.

using the following to exec stproc
exec reportCampaignContacts null, null,'7E7F1614-66EC-46BA-9832-76D98D36452F' ,'8',' LIKE ''%'''
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35506942
Can you post the complete code again. I suppose you made other changes as well. It looks like you changed the order of the parameters at least because you now use:
null, null,'7E7F1614-66EC-46BA-9832-76D98D36452F' .....

You have the
print (@report+@orderby)
line in your procedure. This outputs the query. When you paste this query and run it in a separate window do you also get the
incorrect syntax near the keyword 'ORDER'.
error. If so could you provide this query as well.
0
 

Author Comment

by:Tagom
ID: 35507247
query along gives me results - does not test the autodate case - I do not get the order error


second set of code is actual query - yes the print does give me the query and shows what is being passed, in the original the dates are not getting converted by the date case
code:

select cbKey , cMinStart, cMaxEnd, cLabel , cbAccountNumber ,cbAccountDescription , smsStatusLabel , emailStatusLabel ,
 voiceStatusLabel , cbPriority = case cbPriority when 1 then 'Highest' when 3 then 'Higher' when 5 then 'Normal' when 7 then 'Lower' when 9 then 'Lowest' end ,
 cbPhone1 , cbPhone2 , cbEmail1 , cbEmail2 , cbField0 , cbField1 , cbField2 , cbField3 , cbField4 , cbField5 , cbField6 , 
 cbField7 , cbField8 , cbField9 , cbField10, cbField11 , cbField12 , cbField13 , cbField14 , cbField15 , cbField16 , 
 cbField17  , cbField18 , cbField19 , cbCampaign , cbContactSequence , 
 smsStepKey, smsStepProcessor ,
  smsStatusKey , smsStepUpdateTime , emailStepKey , emailStepProcessor , emailStatusKey ,
   emailStepUpdateTime , voiceStepKey , voiceStepProcessor , voiceStatusKey , voiceStepUpdateTime ,Active = case cPause when 1 then 'No' when 0 then 'Yes' end
from viewContactSummary inner join campaign on cKey = cbCampaign
    WHERE cPause = 0 AND (((cMinStart <=  '1900-01-01 00:00:00'  AND cMinStart !> '2011-01-01 00:00:00'  AND cMaxEnd > '1900-01-01 00:00:00') AND (cMaxEnd <= '2011-01-01 00:00:00'  OR cMaxEnd >= '1900-01-01 00:00:00'))
	OR (cMinStart  >= '1900-01-01 00:00:00' AND cMaxEnd <= '2011-01-01 00:00:00' )) AND cUtility =  '7E7F1614-66EC-46BA-9832-76D98D36452F'  AND cLabel LIKE '%'

Open in new window

USE [acisql2]
GO
/****** Object:  StoredProcedure [dbo].[reportCampaigns]    Script Date: 05/02/2011 13:37:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[reportCampaigns]
	@autoDate int = null,
	@startDate DATETIME = null,
	@endDate DATETIME = null,
	@utilityKey VARCHAR(38),
	@searchString VARCHAR(100)
	
AS
/* Tester:
	exec reportCampaigns null,'1900-01-01 00:00:00', '2011-01-01 00:00:00','7E7F1614-66EC-46BA-9832-76D98D36452F',' LIKE ''%'''
	exec reportCampaigns '6',null, null, '7E7F1614-66EC-46BA-9832-76D98D36452F','LIKE ''%'''

*/

IF LEN(@utilityKey) < 36 
	SET @utilityKey = (SELECT TOP 1 CAST(cUtility AS VARCHAR(38)) FROM campaign)
declare @begin datetime
declare @end datetime
set @begin = cast(@startDate as datetime) 
set @end =cast(@endDate  as datetime)

IF @searchString IS NULL SET @searchString = 'LIKE ''%'' '   

If @autodate is not null
begin
IF @autodate = '0' BEGIN
		SET @begin = DATEADD(yyyy,DATEDIFF(yyyy,0,GETDATE())-20,0)-- all dates
		SET @end = DATEADD(yyyy, DATEDIFF(yyyy,0,getdate())+1, 0)
END		
IF @autodate = '1' BEGIN
	    SET @begin = GETDATE()-- today
		SET @end = DATEADD(dd, DATEDIFF(dd,0,getdate())+1, 0)
END		
IF @autodate = '2'  BEGIN
		SET @begin = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)-- yesterday
		SET @end = GETDATE()
END		
IF @autodate = '3'  BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)-- this week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),+6)
END		
IF @autodate = '4' BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0) -- last week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)
END		
IF @autodate = '5' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE()),0) -- this month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),+30)
END		
IF @autodate = '6' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0) -- last month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),0)
END		
IF @autodate = '7' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)--this year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
END		
IF @autodate = '8' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0) -- last year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
END		
IF @autodate = '9' BEGIN
		SET @begin =  @begin
		SET @end = @end
		
END
end	
IF @startDate IS NULL SET @begin = '1900-01-01 00:00:00'
IF @endDate IS NULL SET @end = '2040-01-01 00:00:00'

DECLARE @start VARCHAR(100)
DECLARE @ends VARCHAR(100)
	
SET @start = CAST(@begin AS VARCHAR(100))
SET @ends = CAST(@end AS VARCHAR(100))


DECLARE @report VARCHAR(4000),@orderby VARCHAR(100)
-- gives correct information for all dates
SET @report ='select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
	Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
	from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
	WHERE (((cMinStart <= '''+ @start +''' AND cMinStart !> '''+ @ends +''' AND cMaxEnd > '''+@start+''') AND (cMaxEnd <= '''+@ends+''' OR cMaxEnd >= '''+@start+'''))
	OR (cMinStart  >= '''+ @start +''' AND cMaxEnd <= '''+@ends+'''))
	AND cUtility = ''' + @utilityKey + ''' AND cLabel = '  + @searchString
	
SET @orderby = ' ORDER BY sDescription, cLabel,Active'

print (@report+@orderby)

exec (@report+@orderby)

Open in new window

0
 

Author Comment

by:Tagom
ID: 35507272
hold the press - let me work something up first I see an error!
0
 

Author Comment

by:Tagom
ID: 35507573
code:
0
 

Author Comment

by:Tagom
ID: 35507582
sorry about that!
code
There are several print statements in there so one can see what is actually being passed.
After taking our the default if null set for startDate and endDate the query executes.
However it now does not execute within the application.
hacking away!

USE [acisql2]
GO
/****** Object:  StoredProcedure [dbo].[reportCampaigns]    Script Date: 05/02/2011 14:15:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[reportCampaigns]
	@autoDate int = null,
	@startDate DATETIME = null,
	@endDate DATETIME = null,
	@utilityKey VARCHAR(38),
	@searchString VARCHAR(100)
	
AS
/* Tester:
	exec reportCampaigns null,'1900-01-01 00:00:00', '2011-01-01 00:00:00','7E7F1614-66EC-46BA-9832-76D98D36452F',' LIKE ''%'''
	exec reportCampaigns '6',null, null, '7E7F1614-66EC-46BA-9832-76D98D36452F','LIKE ''%'''

*/

IF LEN(@utilityKey) < 36 
	SET @utilityKey = (SELECT TOP 1 CAST(cUtility AS VARCHAR(38)) FROM campaign)
declare @begin datetime
declare @end datetime
set @begin = cast(@startDate as datetime) 
set @end =cast(@endDate  as datetime)

IF @searchString IS NULL SET @searchString = 'LIKE ''%'' '   

If @autodate is not null
begin
IF @autodate = '0' BEGIN
		SET @begin = DATEADD(yyyy,DATEDIFF(yyyy,0,GETDATE())-20,0)-- all dates
		SET @end = DATEADD(yyyy, DATEDIFF(yyyy,0,getdate())+1, 0)
END		
IF @autodate = '1' BEGIN
	    SET @begin = GETDATE()-- today
		SET @end = DATEADD(dd, DATEDIFF(dd,0,getdate())+1, 0)
END		
IF @autodate = '2'  BEGIN
		SET @begin = DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)-- yesterday
		SET @end = GETDATE()
END		
IF @autodate = '3'  BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)-- this week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),+6)
END		
IF @autodate = '4' BEGIN
		SET @begin = DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0) -- last week
		SET @end = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)
END		
IF @autodate = '5' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE()),0) -- this month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),+30)
END		
IF @autodate = '6' BEGIN
		SET @begin = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0) -- last month
		SET @end = DATEADD(m,DATEDIFF(m,0,GETDATE()),0)
END		
IF @autodate = '7' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)--this year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
END		
IF @autodate = '8' BEGIN
		SET @begin = DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0) -- last year
		SET @end = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
END		
IF @autodate = '9' BEGIN
		SET @begin =  @begin
		SET @end = @end
		
END
end	

DECLARE @start VARCHAR(100)
DECLARE @ends VARCHAR(100)
	
SET @start = CAST(@begin AS VARCHAR(100))
SET @ends = CAST(@end AS VARCHAR(100))


DECLARE @report VARCHAR(4000),@orderby VARCHAR(100)
-- gives correct information for all dates
SET @report ='select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
	Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
	from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
	WHERE (((cMinStart <= '''+ @start +''' AND cMinStart !> '''+ @ends +''' AND cMaxEnd > '''+@start+''') AND (cMaxEnd <= '''+@ends+''' OR cMaxEnd >= '''+@start+'''))
	OR (cMinStart  >= '''+ @start +''' AND cMaxEnd <= '''+@ends+'''))
	AND cUtility = ''' + @utilityKey + ''' AND cLabel '  + @searchString
	
SET @orderby = ' ORDER BY sDescription, cLabel,Active'
PRINT @autoDate
PRINT @startDate
PRINT @endDate
Print @start
Print @end
print (@report+@orderby)

exec (@report+@orderby)

Open in new window

0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35672257
What I did in a situation like this was this. Change the line
exec (@report+@orderby)
to
select @report+@orderby as Query
Now the stored procedure will return the query not the data. Make a copy op your report (or whereever you are using this) and configure the new report in such a way it displays the query. Copy the query to SSMS and check if it returns any errors.

Also keep in mind that dynamic sql has the permissions of the user executing the code, not of the owner of the procedure.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.
Suggested Courses

580 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