Tim,

You problem is in trying to create dynamic SQL and directly embedding value into the string. This is not a good practice mainly because of SQL injection attacks and because SQL Server will have to generate a new execution plan for your query.

I suggest to take a close look at this classical article by Erland Sommarskog (although quite long):

Dynamic Search Conditions in T-SQL
Version for SQL 2008 (SP1 CU5 and later)

If you insist on your current approach, you need to double a single quote as you pass a character value.

I suggest the following change:

ALTER PROCEDURE [dbo].[AppointmentsSelectByTutorsAndStudentID]
(
	@TutorIDs varchar(500),
	@StudentID char(5)
)
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		AppointmentID,
		TutorID,
		Description
	FROM [dbo].[Appointment]
	WHERE 
		TutorID IN (select Value from dbo.fnSplit(@TutorIDs, ',') OR
		AppointmentID IN (SELECT AppointmentID FROM StudentAppointments WHERE StudentID = @StudentID)
END

There fnSplit could be

CREATE FUNCTION [dbo].[fnSplit]
(@list  VARCHAR(8000),
 @delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
   WITH csvtbl(START, stop) AS (
     SELECT START = 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
     UNION ALL
     SELECT START = stop + 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
                             @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT row_number() over (order by Start) as ID, LTRIM(RTRIM(SUBSTRING(@list, START,
                      CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
         AS VALUE
  FROM   csvtbl
  WHERE  stop > 0

GO

I suggest you to take a look at this blog post Passing multiple ranges to stored procedure I list there several blogs on the topic of string splitting.

There is also an alternative version of your query that may perform better:

SELECT 
		AppointmentID,
		TutorID,
		Description
	FROM [dbo].[Appointment] INNER JOIN dbo.fnSplit(@TutorIDs, ',') F
                    on TutorID = F.Value
	UNION -- to remove duplicates
SELECT 
		AppointmentID,
		TutorID,
		Description
	FROM [dbo].[Appointment]  A
                  where exists (select 1 from StudentAppointments SA       
                   where SA.AppointmentID = A.AppointementID and SA.StudentID = @StudentID)

>Hi,
>
>Was hopiing someone could see why I get this message
>

>Msg 207, Level 16, State 1, Line 8
>Invalid column name 'MMMMM'.
>
>(1 row(s) affected)
>

>
>This is my stored procedure, and the 'MMMMM' is what I passing in as the second parameter.
>
>

>ALTER PROCEDURE [dbo].[AppointmentsSelectByTutorsAndStudentID]
>(
>	@TutorIDs varchar(500),
>	@StudentID char(5)
>)
>AS
>BEGIN
>	SET NOCOUNT ON;
>	DECLARE @SQL varchar(600)
>	
>	SET @SQL = 'SELECT 
>		AppointmentID,
>		TutorID,
>		Description
>	FROM [dbo].[Appointment]
>	WHERE 
>		TutorID IN (' + @TutorIDs + ') OR
>		AppointmentID IN (SELECT AppointmentID FROM StudentAppointments WHERE StudentID = ' + @StudentID + ')' 
>	EXEC(@SQL)
>END
>
>

>
>Here is the window when it runs
>

>USE [StudAthl]
>GO
>
>DECLARE	@return_value int
>
>EXEC	@return_value = [dbo].[AppointmentsSelectByTutorsAndStudentID]
>		@TutorIDs = N'10101, 30303',
>		@StudentID = N'MMMMM'
>
>SELECT	'Return Value' = @return_value
>
>GO
>