Apostrophe cause error in SQL statement

Hi
I have a stored procedure in SQL Server 2000 which is working fine which pulls rows back from a table.

A problem occurs when a string with an apostrophe is entered.

i.e. right here in the code
if @loggedByFilter = "0" set @loggedByString = ''
if @loggedByFilter <> "0" set @loggedByString = 'r.logged_by = ''' + @loggedByFilter + ''' AND '

if @loggedByFilter contains an ' then it causes a crash...

Line 1: Incorrect syntax near 'brien@mydomain'.
Unclosed quotation mark before the character string ' AND r.status = rs.status_id AND r.category = c.category_id AND r.priority = p.priority_id AND r.delivery_team_id = dt.delivery_team_id order by r.request_id DESC'.

So the string paul.'obrien@mydomain.com is what is causing the trouble.

How should I modify the line
if @loggedByFilter <> "0" set @loggedByString = 'r.logged_by = ''' + @loggedByFilter + ''' AND '
to cope with an apostrophe?

Thanks!

CREATE PROCEDURE usp_Request_GetRequests
(
      	@directionData      	int,
      	@statusFilter 		int,
     	@categoryFilter 		int,
     	@priorityFilter 		int,
     	@requestedByFilter 		varchar(255),
     	@loggedByFilter 		varchar(255),
     	@assignedToFilter 		varchar(255),
     	@deliveryTeamFilter 		int,
	@restrictedAccess	varchar(255)
 
) AS
 
DECLARE @sql AS varchar(500)
DECLARE @directionString AS varchar(200)
DECLARE @filterString As varchar(200)
DECLARE @categoryString As varchar(200)
DECLARE @priorityString As varchar(200)
DECLARE @requestedByString As varchar(200)
DECLARE @loggedByString As varchar(200)
DECLARE @assignedToString As varchar(200)
DECLARE @deliveryTeamString As varchar(200)
DECLARE @restrictedAccessString As varchar(300)
 
if @statusFilter = 0 set @filterString =  '' 
if @statusFilter = 1 set @filterString =  'r.status = 1 AND ' 
if @statusFilter = 2 set @filterString =  'r.status = 2 AND ' 
if @statusFilter = 3 set @filterString =  'r.status = 3 AND ' 
if @statusFilter = 4 set @filterString =  'r.status = 4 AND ' 
if @statusFilter = 5 set @filterString =  'r.status = 5 AND ' 
 
if @categoryFilter = 0 set @categoryString =  '' 
if @categoryFilter = 1 set @categoryString =  'r.category = 1 AND ' 
if @categoryFilter = 2 set @categoryString =  'r.category = 2 AND ' 
if @categoryFilter = 3 set @categoryString =  'r.category = 3 AND ' 
if @categoryFilter = 4 set @categoryString =  'r.category = 4 AND ' 
if @categoryFilter = 5 set @categoryString =  'r.category = 5 AND ' 
 
if @priorityFilter = 0 set @priorityString =  '' 
if @priorityFilter = 1 set @priorityString =  'r.priority = 1 AND ' 
if @priorityFilter = 2 set @priorityString =  'r.priority = 2 AND ' 
if @priorityFilter = 3 set @priorityString =  'r.priority = 3 AND ' 
if @priorityFilter = 4 set @priorityString =  'r.priority = 4 AND ' 
 
if @requestedByFilter = "0" set @requestedByString = ''
if @requestedByFilter <> "0" set @requestedByString = 'r.requested_by = ''' + @requestedByFilter + ''' AND '
 
if @loggedByFilter = "0" set @loggedByString = ''
if @loggedByFilter <> "0" set @loggedByString = 'r.logged_by = ''' + @loggedByFilter + ''' AND '
 
--if @assignedToFilter = "All" set @assignedToString = 'r.assigned_to <> ''1'' AND'
if @assignedToFilter = "All" set @assignedToString = ''
if @assignedToFilter = "Anyone" set @assignedToString = 'LEN(r.assigned_to) > 0 AND '
if @assignedToFilter = "Unassigned" set @assignedToString = 'LEN(r.assigned_to) = 0 AND '
if ((@assignedToFilter <> "All") and (@assignedToFilter <> "Anyone") and (@assignedToFilter <> "Unassigned")) set @assignedToString = 'r.assigned_to = ''' + @assignedToFilter + ''' AND '
 
if @deliveryTeamFilter = 0 set @deliveryTeamString =  '' 
--if @deliveryTeamFilter <> 0 set @deliveryTeamString =   'dt.delivery_team_id = ' + @deliveryTeamFilter + ' AND ' 
if @deliveryTeamFilter = 1 set @deliveryTeamString =  'dt.delivery_team_id = 1 AND ' 
if @deliveryTeamFilter = 2 set @deliveryTeamString =  'dt.delivery_team_id = 2 AND ' 
if @deliveryTeamFilter = 3 set @deliveryTeamString =  'dt.delivery_team_id = 3 AND ' 
if @deliveryTeamFilter = 4 set @deliveryTeamString =  'dt.delivery_team_id = 4 AND ' 
if @deliveryTeamFilter = 5 set @deliveryTeamString =  'dt.delivery_team_id = 5 AND ' 
if @deliveryTeamFilter = 6 set @deliveryTeamString =  'dt.delivery_team_id = 6 AND ' 
if @deliveryTeamFilter = 7 set @deliveryTeamString =  'dt.delivery_team_id = 7 AND ' 
if @deliveryTeamFilter = 8 set @deliveryTeamString =  'dt.delivery_team_id = 8 AND ' 
if @deliveryTeamFilter = 9 set @deliveryTeamString =  'dt.delivery_team_id = 9 AND ' 
if @deliveryTeamFilter = 10 set @deliveryTeamString =  'dt.delivery_team_id = 10 AND ' 
if @deliveryTeamFilter = 11 set @deliveryTeamString =  'dt.delivery_team_id = 11 AND ' 
if @deliveryTeamFilter = 12 set @deliveryTeamString =  'dt.delivery_team_id = 12 AND ' 
if @deliveryTeamFilter = 13 set @deliveryTeamString =  'dt.delivery_team_id = 13 AND ' 
if @deliveryTeamFilter = 14 set @deliveryTeamString =  'dt.delivery_team_id = 14 AND ' 
if @deliveryTeamFilter = 15 set @deliveryTeamString =  'dt.delivery_team_id = 15 AND ' 
if @deliveryTeamFilter = 16 set @deliveryTeamString =  'dt.delivery_team_id = 16 AND ' 
if @deliveryTeamFilter = 17 set @deliveryTeamString =  'dt.delivery_team_id = 17 AND ' 
 
if @directionData = 0 set @directionString = 'order by r.request_id ASC'
if @directionData = 1 set @directionString = 'order by r.request_id DESC'
if @directionData = 2 set @directionString = 'order by r.requested_by ASC'
if @directionData = 3 set @directionString = 'order by r.requested_by DESC'
if @directionData = 4 set @directionString = 'order by r.logged_by ASC'
if @directionData = 5 set @directionString = 'order by r.logged_by DESC'
if @directionData = 6 set @directionString = 'order by r.assigned_to ASC'
if @directionData = 7 set @directionString = 'order by r.assigned_to DESC'
if @directionData = 8 set @directionString = 'order by r.date_logged ASC'
if @directionData = 9 set @directionString = 'order by r.date_logged DESC'
if @directionData = 10 set @directionString = 'order by r.status ASC'
if @directionData = 11 set @directionString = 'order by r.status DESC'
if @directionData = 12 set @directionString = 'order by r.category ASC'
if @directionData = 13 set @directionString = 'order by r.category DESC'
if @directionData = 14 set @directionString = 'order by r.priority ASC'
if @directionData = 15 set @directionString = 'order by r.priority ASC'
 
if @restrictedAccess <> '' set @restrictedAccessString = 'dt.delivery_team_id IN ' + @restrictedAccess + ' AND '
if @restrictedAccess = '' set @restrictedAccessString = ''
 
SELECT @sql='SELECT r.*, rs.*, c.*, p.*, dt.* FROM requests AS r, requests_status AS rs, categories AS c, priorities AS p, delivery_teams AS dt WHERE ' + @filterString + @categoryString + @priorityString + @requestedByString + @loggedByString + @assignedToString + @deliveryTeamString + @restrictedAccessString + 'r.status = rs.status_id AND r.category = c.category_id AND r.priority = p.priority_id AND r.delivery_team_id = dt.delivery_team_id ' + @directionString
 
EXEC (@sql)
GO

Select allOpen in new window