Stored procedure

[vlitim]

I have a sp below using the command

execute sp_SummarySearch 'a',3

to run this


CREATE PROCEDURE sp_SummarySearch
(
@searchterm varchar(255),
@type int
)
AS

DECLARE @SQL varchar(5000)


SELECT @sql='SELECT dbo.Enquiries.ClientStaffID, dbo.Enquiries.id, dbo.ClientsStaff.fname, dbo.ClientsStaff.sname, dbo.Enquiries.problem, dbo.Enquiries.targetDate,
dbo.Enquiries.completedDate, dbo.helpDesk_IT.fname AS Expr1, dbo.helpDesk_IT.sname AS Expr2, dbo.Department.name,
dbo.Department.id AS Expr3, dbo.Enquiries.helpDeskID
FROM dbo.Enquiries INNER JOIN
dbo.ClientsStaff ON dbo.Enquiries.ClientStaffID = dbo.ClientsStaff.id LEFT OUTER JOIN
dbo.helpDesk_IT ON dbo.Enquiries.helpDeskID = dbo.helpDesk_IT.id LEFT OUTER JOIN
dbo.Department ON dbo.Enquiries.DepartmentID = dbo.Department.id'

if @type=3
begin
Select @sql=@sql+' WHERE dbo.Enquiries.problem LIKE ''%'+ @searchterm +'%'' OR dbo.Enquiries.resolution LIKE ''%'+@searchterm +'%'
end

EXEC(@sql)
GO


but I get the below error

Server: Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark before the character string '%a%'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '%a%'.

what am I doing wrong??

 

[bperry]

When you use the single quotes around the wildcards, SQL thinks you mean the end of the string. So you need to find another way to say '

CHAR(39) which is the ascii code for '

this should work, but I always find these things a little tricky.

@declare @wc1 char(2), @wc2 char(2)
set @wc1 = CHAR(39) + '%'
set @wc2 = '%' + CHAR(39)

begin
Select @sql=@sql+
'WHERE dbo.Enquiries.problem LIKE ' + @wc1 + @searchterm + @wc2 +
' OR dbo.Enquiries.resolution LIKE ' + @wc1 + @searchterm + @wc2
end

 

[vlitim]

now I am trying to do this with dates as well

sp_SummarySearch '',1,'04/20/2002','04/22/2002'

CREATE PROCEDURE sp_SummarySearch
(
@searchterm varchar(255),
@type int,
@startDate datetime,
@endDate datetime
)
AS

DECLARE @SQL varchar(5000)


SELECT @sql='SELECT dbo.Enquiries.ClientStaffID, dbo.Enquiries.id, dbo.ClientsStaff.fname, dbo.ClientsStaff.sname, dbo.Enquiries.problem, dbo.Enquiries.targetDate,
dbo.Enquiries.completedDate, dbo.helpDesk_IT.fname AS Expr1, dbo.helpDesk_IT.sname AS Expr2, dbo.Department.name,
dbo.Department.id AS Expr3, dbo.Enquiries.helpDeskID
FROM dbo.Enquiries INNER JOIN
dbo.ClientsStaff ON dbo.Enquiries.ClientStaffID = dbo.ClientsStaff.id LEFT OUTER JOIN
dbo.helpDesk_IT ON dbo.Enquiries.helpDeskID = dbo.helpDesk_IT.id LEFT OUTER JOIN
dbo.Department ON dbo.Enquiries.DepartmentID = dbo.Department.id'

if @type=1
begin
Select @sql=@sql +' WHERE completedDate >= '+@startdate+' And completedDate <= '+@enddate+' AND completed=1 AND (problem LIKE ''%'+ @searchterm + '%'' OR resolution LIKE ''%'+ @searchterm + '%'')'
end
if @type=2
begin
Select @sql=@sql +' SELECT * FROM enquiries WHERE completed=1 AND (problem LIKE ''%'+ @searchterm + '%'' OR resolution LIKE ''%' + @searchterm + '%'')'
end
if @type=3
begin
Select @sql=@sql+' WHERE dbo.Enquiries.problem LIKE ''%'+ @searchterm +'%'' OR dbo.Enquiries.resolution LIKE ''%'+@searchterm +'%'' OR dbo.Department.name=1'
end

EXEC(@sql)
GO


and I get the error
Server: Msg 241, Level 16, State 1, Procedure sp_SummarySearch, Line 23
Syntax error converting datetime from character string.

which is the line that reads

Select @sql=@sql +' WHERE completedDate >= '+@startdate+' And completedDate <= '+@enddate+' AND completed=1 AND (problem LIKE ''%'+ @searchterm + '%'' OR resolution LIKE ''%'+ @searchterm + '%'')'

 

[linda105]

This looks to me like a problem with the date format e.g. dd/mm/yy or mm/dd/yy - Do you get the same problem if you pass through dates like '04/04/2002' rather than '04/20/2002'?

If this is the problem then you could try either passing the string in the SQL server default format (this depends on the language set up US English will be mdy), set the date format at the start of the procedure (using SET DATEFORMAT), pass the date string in a way that can't be confused (e.g. text description of the month Apr 02 2002) or use the convert statement in your query (eg

where completed Date >= convert(datetime,mystr,101)

There is more info on the number part in the TSQL dictionary but
101 USA mm/dd/yyyy
103 British/French dd/mm/yyyy
)

 

[vlitim]

tried what you suggested but with no luck, I think the probelm may lie in trying to put the date in the @sql string and then executing it!

 

[tlbroadbent]

The date format should work but because you are inserting the values in the SQL string, you need to enclose the dates in quotes just as you do any other string. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.