Consulta de Filtro SQL

Eu tenho uma tabela com vários campos nela. Eu estou tentando criar filtro de pesquisa no asp.net para que o usuário possa pesquisar por um ou por uma combinação de campos. Então, basicamente, eu quero criar um único procedimento armazenado que leva em 4 parâmetros e ele irá acrescentar o parâmetro à cláusula WHERE se não for nulo …

O TableExample tem 4 colunas, Col1 Col2 Col3 Col4 Col4

Espero que haja uma maneira de fazer isso com um único procedimento armazenado em vez de criar um para cada combinação possível.

Eu estava tentando algo assim, o que não é correto, mas é o que eu tenho até agora.

OBRIGADO!

CREATE PROCEDURE [dbo].[Search] @Col1 int, @Col2 int, @Col3 int, @Col4 int AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM [dbo].[TestTable] WHERE 1=1 CASE WHEN @Col1 IN NOT NULL THEN AND [Col1] = @Col1 WHEN @Col2 IN NOT NULL THEN AND [Col2] = @Col2 WHEN @Col3 IN NOT NULL THEN AND [Col3] = @Col3 WHEN @Col4 IN NOT NULL THEN AND [Col4] = @Col4 END 

Use o fato de que OU curto-circuitos. Eu assumi que -1 não é um valor válido.

 CREATE PROCEDURE [dbo].[Search] @Col1 int = -1, @Col2 int = -1, @Col3 int = -1, @Col4 int = -1 AS Begin SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM [dbo].[TestTable] WHERE (@Col1 = -1 OR [Col1] = @Col1) and (@Col2 = -1 OR [Col2] = @Col2) and (@Col3 = -1 OR [Col3] = @Col3) and (@Col4 = -1 OR [Col4] = @Col4) END 

search é uma das raras opções que eu prego usando sql dynamic ou construindo sua string sql no código. se você tiver um ambiente all sproc, use sql dynamic no seu sproc. parametrize-o e use o sp_executeSQL para executá-lo para evitar a injeção de SQL

Você pode fazer isso com um método semelhante ao que você tem:

 WHERE CASE WHEN @Col1 IS NULL THEN true ELSE [Col1] = @Col1 END AND CASE WHEN @Col2 IS NULL THEN true ELSE [Col2] = @Col2 END ... 

Ou você pode torná-lo muito mais simples, embora possivelmente menos legível:

 WHERE (@Col1 IS NULL OR [Col1] = @Col1]) AND (@Col2 IS NULL OR [Col2] = @Col2]) AND ... 

Você teria que usar SQL dynamic para fazer isso:

 CREATE PROCEDURE [dbo].[Search] @Col1 int, @Col2 int, @Col3 int, @Col4 int AS DECLARE @SQL nvarchar(MAX) SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET @SQL = 'SELECT * FROM [dbo].[TestTable] WHERE 1=1 ' IF @Col1 IS NOT NULL SET @SQL = @SQL + ' AND Col1 = ''' + @Col1 + ''' ' IF @Col2 IS NOT NULL SET @SQL = @SQL + ' AND Col2 = ''' + @Col2 + ''' ' IF @Col3 IS NOT NULL SET @SQL = @SQL + ' AND Col3 = ''' + @Col3 + ''' ' IF @Col4 IS NOT NULL SET @SQL = @SQL + ' AND Col4 = ''' + @Col4 + ''' ' exec sp_executesql @SQL END 

Tenha em mente que há perigos nisso, incluindo injeção de SQL, bem como uma série de outros problemas de permissions que podem surgir, já que é SQL dynamic, mas é a única maneira de conseguir isso na camada de database. Se você quer construir sua consulta na camada de aplicação (em C #), você pode se defender contra os ataques de injeção SQL muito mais detalhadamente.

Alguns links SQL dynamics que podem ajudar você a entender as desvantagens:

http://www.sommarskog.se/dynamic_sql.html http://slashstar.com/blogs/tim/archive/2006/10/12/The-Prevalence-and-Dangers-of-SQL-Injection.aspx

Agradeço a todos por suas respostas. No entanto, eu fiz um pouco diferente. Espero que ajude alguém! Aqui está como eu fui sobre isso:

 CREATE PROCEDURE [dbo].[TestTable_Search] @Col1 int, @Col2 uniqueidentifier, @Col3 datetime, @Col4 datetime AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM [dbo].[TestTable] WHERE [Col1] = COALESCE(@Col1, Col1) AND [Col2] = COALESCE(@Col2, Col2) AND [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND [Col4] <= COALESCE(@Col4 + "23:59:59", Col4) 
Intereting Posts