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??
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??