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

Your parameter data length would be 1 char only as you don't have specify the data lenght. — Yogesh Sharma, Jul 18 '19 at 12:26

score 1 · Accepted Answer · answered Jul 18 '19 at 12:29

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

SQL server function how to use parameter to in query

1 Answers1