Where do my parameter variables go in T-SQL function?

Question

This is the function that I am trying to make below with two parameters and one single output that is the matching words. I am using @searchentry and @bestmatch for my parameters. My questions is where should the parameters go in the function so that I can just call the function when it is created Select dbo.FunMatch('enamel cleaner', 'cleaner') it will excecute the function and return the matching words from the two arguments which would be 1 ?

Create Function dbo.FunMatch(
    @searchentry varchar,
    @bestmatch varchar
    ) 
    Returns INT
    As
    Begin
    Declare @output INT
    Set @output = (select
    @searchentry,
    @bestmatch,
    cast(count(isMatch) as float) as matchingWords
from
(
    select 
        s.value as word_from_search_entry_txt, 
        b.value as word_from_best_match,
        case 
            when s.value = b.value or s.value+'s'=b.value or s.value=b.value+'s' then 'match' 
            else null 
        end as isMatch,
        t.*
     from (
        SELECT 
            @searchentry,@bestmatch
        FROM #tmp_parts
        
    ) t
    cross apply 
    string_split(@searchentry, ' ') s
    cross apply
    string_split(@bestmatch, ' ') b
) a
group by
    @searchentry,
    @bestmatch)
        
        Return @output

I am writing a function to return the matching words between two strings. example data below

CREATE TABLE #tmp_parts
(
search_entry_txt VARCHAR(30),
best_match VARCHAR(30),
);
INSERT INTO #tmp_parts
VALUES ('rotating conveyors', 'conveyor'),
('rivet tool', 'rivet nut tool'),
('enamel cleaner', 'cleaner'),
('farm ring', 'ring'),
('tire gauge', 'gauge'),
('ice cream','ice cream');

You can see the expected out here which is the matchingWords column

select
    search_entry_txt,
    best_match,
    cast(count(isMatch) as float) as matchingWords
from
(
    select 
        s.value as word_from_search_entry_txt, 
        b.value as word_from_best_match,
        case 
            when s.value = b.value or s.value+'s'=b.value or s.value=b.value+'s' then 'match' 
            else null 
        end as isMatch,
        t.*
     from (
        SELECT 
            search_entry_txt,best_match
        FROM #tmp_parts
    ) t
    cross apply 
    string_split(search_entry_txt, ' ') s
    cross apply
    string_split(best_match, ' ') b
) a
group by
    search_entry_txt,
    best_match

You want to match 'new' to 'news'? That seems peculiar.

HABO
– HABO

2022-03-06 18:40:17 +00:00
Commented Mar 6, 2022 at 18:40 — HABO
– HABO, Commented Mar 6, 2022 at 18:40

D-Shih · Accepted Answer · 2022-03-05 14:39:18Z

1

There are some issues with your function script.

The parameters @searchentry, @bestmatch might add type length otherwise that will declare length as 1.
you are missing the END on the function end.
from your code you don't need to use #tmp_parts temp table, just use parameters @searchentry, @bestmatch.
There are some verbosity script you might not need, (group by part, subquery which be able to use aggregate function to instead)

I had rewritten your script, you can try this.

Create Function dbo.FunMatch(
    @searchentry varchar(max),
    @bestmatch varchar(max)
) 
Returns INT
As
Begin
    Declare @output INT
    set @output =(select 
    COUNT(case 
        when s.value = b.value or s.value+'s'=b.value or s.value=b.value+'s' then 'match' 
        else null 
    end) 
    from 
    string_split(@searchentry, ' ') s
    cross apply
    string_split(@bestmatch, ' ') b)
    Return @output
END

sqlfiddle

answered Mar 5, 2022 at 14:39

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stu Over a year ago

In addition to this, it could be a table-valued function just returning the result of the single select statement.

Collectives™ on Stack Overflow

Where do my parameter variables go in T-SQL function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related