Passing value of a VARCHAR variable in the WHERE IN command

Question

Passing value of a VARCHAR variable in the WHERE IN command

Navigation

#1 by (4 votes)
#2 by (1 votes)

1

Good evening.

I have the following example procedure

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 concurrent values, such as Sales and Marketing.

I called the procedure this way

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

But it does not return anything. I made a test by checking the value that is written to a variable

'Marketing','Vendas'

Taking this value and passing it directly into the query returns the values quietly.

Any suggestions on what I can do in the code?

sql stored-procedures

asked by anonymous 14.01.2016 / 01:30

2 answers

4

Your error is probably in the call, try this:

EXEC sp_listaFuncionarios @nome_dept = 'Marketing'

You will have trouble passing the parameter with these quotes. It might be best to filter by the department id if this column exists, like this:

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'

14.01.2016 / 01:38

Resolve undifined index [closed] JOIN 3 different tables display list

score 1 · Accepted Answer

Create a procedure that converts an array to a list of words.

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'