Hmm, I like this approach using the function but I am a bit embarassed to say I am not sure what type of function that would be. Where do I put this? There are several categories under functions.


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