SQL server function how to use parameter to in query

Question

my sql function is not working parameter value. If provide hard code value it will give me results.

ALTER function [dbo].[team_concat] (@input varchar)
returns varchar(8000)
as
BEGIN
declare @putout varchar(8000)
set @putout = null

-- select @putout = COALESCE(IsNull(@putout+ ', ', ''), '') + team_residence_location from programs where team_combo = 'Hartford CIS / Coaching'
select @putout = COALESCE(IsNull(@putout+ ', ', ''), '') + team_residence_location from programs where team_combo = @input


return @putout

Answer 1

Try this:

    ALTER function [dbo].[team_concat] (@input varchar(100))  --- specify length
    returns varchar(8000)
    as
    BEGIN
    declare @putout varchar(8000)
    set @putout = null

    -- select @putout = COALESCE(IsNull(@putout+ ', ', ''), '') + team_residence_location from programs where team_combo = 'Hartford CIS / Coaching'
    select @putout = COALESCE(IsNull(@putout+ ', ', ''), '') + team_residence_location from programs where team_combo = @input


    return @putout