SQL Compact: Utilizar o IN (…) com um comando preparado

Já não é novidade para ninguém que a preparação de um comando que precisa de ser executado múltiplas vezes com diferentes valores melhora de forma muito sensivel a performance de execução da 2ª execução e das seguintes (no SQL Compact, o comando é efectivamente preparado na 1ª execução e não na invocação do método .Prepare). Para além disso, usar comandos SQL com parâmetros elimina os problemas por muitos conhecidos de ‘qual o formato da data’ ou ‘qual o separador decimal’ a utilizar ao passar valores deste tipo para o comando a executar.

Os parâmetros podem ser utilizados da forma mais convencional, como em…

UPDATE stocks SET qtd=@novaQtd WHERE artigo=@artigo

…mas uma questão encontrada com frequência é, como preparar um comando com uma cláusula WHERE com o operador IN, em que o nº de valores a procurar é desconhecido à partida ou até variável?

UPDATE stocks SET qtd=0 WHERE artigo IN (@artigo1, @artigo2, …, @artigon)

Nas versões completas do SQL, a solução proposta passa quase sempre por criar uma stored procedure para o efeito, mas o SQL Compact não suporta stored procedures, pelo que parecia não haver solução para este problema.

Ao encontrar esta questão pela enésima vez num fórum, reflecti mais um pouco e apresentei uma solução que produz o mesmo efeito.

Vamos supor que temos uma tabela clientes, da qual queremos actualizar o campo activo para 1 de acordo com a lista de códigos postais escolhidos pelo utilizador.

Para o efeito podíamos preparar o seguinte comando…

UPDATE clientes SET activo = 1 WHERE Patindex(‘[‘ + codigopostal + ‘]’, @codPostal) > 0

onde codigopostal é o campo da tabela de clientes com os códigos a procurar, e @codPostal um parâmetro a ser definido do tipo nvarchar. A função Patindex devolve a posição em que a 1ª string é encontrada na 2ª, devolvendo um valor superior a zero sempre que seja encontrada.

Para utilizá-lo, bastaria passar como valor para o parâmetro @codPostal, a lista de códigos escolhidos pelo utilizador, tendo o cuidado de ao compor a string com os mesmos, colocar parentesis rectos à volta de cada um deles, como por ex.:

[3850-000]{3850-010][3000-000]

Os parentesis rectos servem para ‘isolar’ cada um dos valores a ser procurados (ex., ao procurar mar, não fazer match com Maria, ou Tomar), assumindo que esses caracteres não são esperados dentro dos valores a serem pesquisados!

Caso queiram procurar datas ou valores numéricos, terão de os converter para string de modo a poderem ser concatenados com os parantesis rectos.