fnSplitString

/*

fnSplitString is useful for use in stored procedures that are consumed from SSRS reports. These
reports pass parameter values into the stored procedure. When the parameter in the SSRS report
allows multiple values, they are passed to the stored procedure as a comma delimited string. 
fnSplitString allows us to select data based on the them easily.

STRING_SPLIT introduced in SQL2016 making this function unnecessary

*/


CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(value NVARCHAR(MAX))

BEGIN 

	-- alternative way to declare and set values for variables. Can use SET instead of SELECT when
	-- assigning to one variable 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 

    WHILE @start < LEN(@string) + 1 BEGIN 

        -- end is zero if the delimiter was't found in the string, 
		-- so set it to just past the end of the string to retrieve the 
		-- remainder of it
		IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (value)  
        VALUES (SUBSTRING(@string, @start, @end - @start)) 

        SET @start = @end + 1 

        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 

    RETURN 

END

GO

select value from fnSplitString('20173,20175', ',')

select *
	from StudentHistory
	where TermID in (select value from fnSplitString('20173,20175', ','))

select sh.*
	from StudentHistory sh
	join fnSplitString('20173,20175', ',') t
	on sh.TermId=t.Value


GO



---
--- set vs select
---

declare @name varchar(4000) = ''
select @name = LastName from dbo.Student
select @name
go

declare @name varchar(4000) = ''
set @name = (select LastName from dbo.Student)
select @name
go

-- fancy (or just complicated) stuff
declare @name varchar(4000) = NULL
select @name = isnull(@name + ', ' + LastName, LastName) from dbo.Student
select @name
go