Passing value of a VARCHAR variable in the WHERE IN command

Question

good night.

I have the following example process

CREATE PROCEDURE sp_listaFuncionarios
@nome_dept varchar(50)
as
SELECT * FROM Funcionarios WHERE nome_departamento IN (@nome_dept)

If during the Procedure call you want to pass two simultaneous values such as : Sales and Marketing.

I called the trial that way

EXEC sp_listaFuncionarios '''Marketing'',''Vendas'''

But nothing comes back. I did a test by checking the value that is saved in a variable

'Marketing','Vendas'

Taking this value and going straight to the query, returns the values quietly.

Any hints of what I can do in the code?

Answer 1

Probably your error is in the call, try it this way:

EXEC sp_listaFuncionarios @nome_dept = 'Marketing'

You will have problems passing the parameter with these quotes. Perhaps it is better to filter the department id if that column exists, that way:

CREATE PROCEDURE sp_listaFuncionarios
@id_dept varchar(50)
as
SELECT * FROM Funcionarios WHERE nome_departamento IN (' + @id_dept + ')'

EXEC sp_listaFuncionarios @id_dept = '1,2,3'

Answer 2

Create a precedent that converts an array into a word list.

CREATE PROCEDURE [dbo].[sp_ArrayToString]
    -- Add the parameters for the stored procedure here
    @ToString Varchar(1000)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #TempList (Strings Varchar(50))

    DECLARE @IDs varchar(50), @Pos int

    SET @ToString = LTRIM(RTRIM(@ToString))+ ','
    SET @Pos = CHARINDEX(',', @ToString, 1)

    IF REPLACE(@ToString, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @IDs = LTRIM(RTRIM(LEFT(@ToString, @Pos - 1)))
            IF @IDs <> ''
            BEGIN
                INSERT INTO #TempList (Strings) VALUES (CAST(@IDs AS Varchar(50))) --Use Appropriate conversion
            END
            SET @ToString = RIGHT(@ToString, LEN(@ToString) - @Pos)
            SET @Pos = CHARINDEX(',', @ToString, 1)

        END
    END 

    Select Strings From #TempList
    Drop Table #TempList

END

In your query do something like this:

CREATE PROCEDURE sp_listaFuncionarios
@nome_dept varchar(50)
as

    CREATE TABLE #TempList (dpt varchar(50))
    Insert Into #TempList (dpt) 
        Exec sp_ArrayToString @nome_dept


SELECT * FROM Funcionarios WHERE nome_departamento IN (select dpt from #TempList)

Pass the parameter like this:

EXEC sp_listaFuncionarios 'Marketing,Vendas'