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'
Don’t forget to drop after using the #Templist. Drop Table #Templist
– Alexandre Previatti
Thanks for the tip.
– Enio Lacerda