How to perform a search in a table with given more than one paramaters (SQL SERVER EXPRESS 2008)?
-
Hi, I need an approach to perform an advance search in one table with given choice of multiple fields in SQL SERVER EXPRESS 2008. For example: If I wan't to edit a customer record with given mutiple paramaters such as (Customer Number, Email, First Name, Last Name, Phone Number), the user can submit more than one field value for the search. It is easy to do that in ADO.NET but how it would be possible to perfom this with a SELECT statement inside a stored procedure to retrive the record according to given some of the paramaters. If you give me an example, it would make my day ;) Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
-
Hi, I need an approach to perform an advance search in one table with given choice of multiple fields in SQL SERVER EXPRESS 2008. For example: If I wan't to edit a customer record with given mutiple paramaters such as (Customer Number, Email, First Name, Last Name, Phone Number), the user can submit more than one field value for the search. It is easy to do that in ADO.NET but how it would be possible to perfom this with a SELECT statement inside a stored procedure to retrive the record according to given some of the paramaters. If you give me an example, it would make my day ;) Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
This is the sort of thing you need
select {column list}
from {table}
where (name = @name or postcode = @postcode)Of course its up to you to decide whether to use OR or AND, and you also need to deal with nulls. Hope this helps
Bob Ashfield Consultants Ltd
-
Hi, I need an approach to perform an advance search in one table with given choice of multiple fields in SQL SERVER EXPRESS 2008. For example: If I wan't to edit a customer record with given mutiple paramaters such as (Customer Number, Email, First Name, Last Name, Phone Number), the user can submit more than one field value for the search. It is easy to do that in ADO.NET but how it would be possible to perfom this with a SELECT statement inside a stored procedure to retrive the record according to given some of the paramaters. If you give me an example, it would make my day ;) Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
that is a report procedure not a edit one(add/edit/delete), you may want to use crystal report display on that procedure. that a look in this i made for CD albums search: (table album has = id_album,...,album_name,id_singer,id_record_company,year,id_song1,id_song2,...id_song20 other tables have primarykeys)
create procedure album_search
@autor as int = null, -- singer
@editora as int = null, -- record company
@genero as int = null, -- pop,jazz,country,rock ect
@ano as int = null, -- year
@faixa varchar(50)= null -- single songas
begin
select al.id_album as 'ID',al.data_registo as 'Data Registo',al.nome as 'Nome',au.nome as 'Autor',ed.nome as 'Editora',ge.nome as 'Genero',al.ano as 'Ano',fa1.nome as 'Faixa 1',fa2.nome as 'Faixa 2',fa3.nome as 'Faixa 3',fa4.nome as 'Faixa 4',fa5.nome as 'Faixa 5',fa6.nome as 'Faixa 6',fa7.nome as 'Faixa 7',fa8.nome as 'Faixa 8',fa9.nome as 'Faixa 9',fa10.nome as 'Faixa 10',fa11.nome as 'Faixa 11',fa12.nome as 'Faixa 12',fa13.nome as 'Faixa 13',fa14.nome as 'Faixa 14',fa15.nome as 'Faixa 15',fa16.nome as 'Faixa 16',fa17.nome as 'Faixa 17',fa18.nome as 'Faixa 18',fa19.nome as 'Faixa 19',fa20.nome as 'Faixa 20'
from dbo.albuns as al
inner join dbo.AUX_autores as au on al.id_autor = au.id_autor
inner join dbo.AUX_editoras as ed on al.id_editora = ed.id_editora
inner join dbo.AUX_generos as ge on al.id_genero = ge.id_genero
inner join dbo.faixas as fa1 on al.id_faixa1 = fa1.id_faixa
inner join dbo.faixas as fa2 on al.id_faixa2 = fa2.id_faixa
inner join dbo.faixas as fa3 on al.id_faixa3 = fa3.id_faixa
inner join dbo.faixas as fa4 on al.id_faixa4 = fa4.id_faixa
inner join dbo.faixas as fa5 on al.id_faixa5 = fa5.id_faixa
inner join dbo.faixas as fa6 on al.id_faixa6 = fa6.id_faixa
inner join dbo.faixas as fa7 on al.id_faixa7 = fa7.id_faixa
inner join dbo.faixas as fa8 on al.id_faixa8 = fa8.id_faixa
inner join dbo.faixas as fa9 on al.id_faixa9 = fa9.id_faixa
inner join dbo.faixas as fa10 on al.id_faixa10 = fa10.id_faixa
inner join dbo.faixas as fa11 on al.id_faixa11 = fa11.id_faixa
inner join dbo.faixas as fa12 on al.id_faixa12 = fa12.id_faixa
inner join dbo.faixas as fa13 on al.id_faixa13 = fa13.id_faixa
inner join dbo.faixas as fa14 on al.id_faixa14 = fa14.id_faixa
inner join dbo.faixas as fa15 on al.id_faixa15 = fa15.id_faixa
inner join dbo.faixas as fa16 on al.id_faixa16 = fa16.id_faixa
inner join dbo.faixas as fa17 on al.id_faixa17 = fa17.id_faixa
inner join dbo.faixas as fa18 on al.id_faixa18 = fa18.id_faixa
inner join dbo.faixas as fa -
Hi, I need an approach to perform an advance search in one table with given choice of multiple fields in SQL SERVER EXPRESS 2008. For example: If I wan't to edit a customer record with given mutiple paramaters such as (Customer Number, Email, First Name, Last Name, Phone Number), the user can submit more than one field value for the search. It is easy to do that in ADO.NET but how it would be possible to perfom this with a SELECT statement inside a stored procedure to retrive the record according to given some of the paramaters. If you give me an example, it would make my day ;) Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
Hi I guess you could try something like this. CREATE PROCEDURE client_stuff @variable1 int = NULL, @variable2 varchar(10) = NULL, @variable3 double = NULL AS SELECT * FROM mytable t1 WHERE client_id = ISNULL(@variable1,t1.client_id) AND client_name = ISNULL(@variable2, t1.client_name) AND client_balance = ISNULL(@variable3, t1.client_balance) ... etc I hope that is the right syntax for your DB. Basically you pass in NULLABLE parameters which may or may not be specified, then in the select use ISNULL to replace any NULL values with the value in the column, so if NULL then give me everything in this column, otherwise give me only records whose value in the column match the parameter. I hope that helps, Cheers, Kevin