Query problem - TIPOS IN ()

Hi,

I have the following query

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE CONSTELEFONICA
@.E1 VARCHAR(50),
@.TIPOS VARCHAR(50),
@.PERINI DATETIME,
@.PERFIM DATETIME,
@.PERINI2 DATETIME,
@.PERFIM2 DATETIME
AS
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI as DATA_INICIAL, @.PERFIM as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI AND @.PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA IN (@.TIPOS)
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI2 as DATA_INICIAL, @.PERFIM2 as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI2 AND @.PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA NOT IN (@.TIPOS)
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

And from my application I call the query like...

EXEC CONSTELEFONICA '01133722000', 'CONUR, LOCAL', '20060205','20060304 23:59:59.997', '20060203','20060302 23:59:59.997'

But it doesn't work because the CLASSIFICA_TELEFONICA NOT IN (@.TIPOS) does not recognise as CLASSIFICA_TELEFONICA NOT IN ('CONUR', 'LOCAL') how can I make this query ?

Thanks

You can't do it like that, read Arrays and Lists in SQL Server by SQL Server MVP Erland Sommarskog (http://www.sommarskog.se/arrays-in-sql.html)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

I understood but I don't know how to apply in my query can you help me ?

Thanks

|||

I tried doing this

SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI as DATA_INICIAL, @.PERFIM as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@.TIPOS) I ON T.CLASSIFICA_TELEFONICA = I.str WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI AND @.PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI2 as DATA_INICIAL, @.PERFIM2 as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@.TIPOS) I ON T.CLASSIFICA_TELEFONICA <> I.str WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI2 AND @.PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA

But I got the message that CHARLIST_TO_TABLE_SP is not a valid function...but in the reference site it's a procedure...and I've created it

|||Look for the function: iter_charlist_to_table for the 2000 way to do it. That was the 7.0 method, since we didn't have functions back then.sql