Retrieving parameter from sql table to use in procedure

Question

I have this procedure in SQL Server to check if word exists in table, if exist update table, if not add word into table.

CREATE PROCEDURE raw_add @word nvarchar (100)
AS
BEGIN
    IF EXISTS(SELECT * FROM Connection.dbo.Dict WHERE game + '%' LIKE @word + '%')
        BEGIN
            UPDATE Dict SET count=count+1 WHERE game + '%' LIKE @word + '%'
        END
    ELSE
        BEGIN
            INSERT INTO Dict (mongol) VALUES (@word)
        END
END

I can execute procedure.

EXECUTE raw_add 'dota'

But rather than typing every single word, I want to take words in another table as parameters for procedure. I have this code, but getting no parameter was supplied error.

EXECUTE raw_add SELECT words_to_add
FROM Connection.dbo.raw p
CROSS APPLY string_split(p.words_to_add, ' ')

Answer 1

You can't do exec <procedure> select <parameters>, and even if you code, that would make little sense because it would be RBAR - meaning you will run your procedure Row By Agonizing Row (Acronym source: Jeff Moden).

What you need to do is write a new procedure, that will do the upsert with a set based apporach.

SQL Server has a Merge command for upserts that you can use for that.

DECLARE @words AS TABLE (word nvarchar(100))

INSERT INTO @words (word)
-- assuming that's the name of the column from the string split
SELECT /*perhaps DISTINCT here?*/ Item 
FROM Connection.dbo.raw p
CROSS APPLY string_split(p.words_to_add, ' ')

MERGE Connection.dbo.Dict  AS target  
USING (SELECT word FROM @words) AS source (word) 
    ON (target.game LIKE '%' + source.word + '%')  
WHEN MATCHED THEN   
    UPDATE SET count += 1
WHEN NOT MATCHED THEN  
    INSERT (mongol)  
    VALUES (source.word)