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
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 song
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 fa19 on al.id_faixa19 = fa19.id_faixa
inner join dbo.faixas as fa20 on al.id_faixa20 = fa20.id_faixa
where au.id_autor = isnull(@autor,au.id_autor)
and ed.id_editora = isnull(@editora,ed.id_editora)
and ge.id_genero = isnull(@genero,ge.id_genero)
and al.ano = isnull(@ano,al.ano)
and (fa1.nome like '%' + isnull(@faixa,fa1.nome) + '%'
or fa2.nome like '%' + isnull(@faixa,fa2.nome) + '%'
or fa3.nome like '%' + isnull(@faixa,fa3.nome) + '%'
or fa4.nome like '%' + isnull(@faixa,fa4.nome) + '%'
or fa5.nome like '%' + isnull(@faixa,fa5.nome) + '%'
or fa6.nome like '%' + isnull(@faixa,fa6.nome) + '%'
or fa7.nome like '%' + isnull(@faixa,fa7.nome) + '%'
or fa8.nome like '%' + isnull(@faixa,fa8.nome) + '%'
or fa9.nome like '%' + isnull(@faixa,fa9.nome) + '%'
or fa10.nome like '%' + isnull(@faixa,fa10.nome) + '%'
or fa11.nome like '%' + isnull(@faixa,fa11.nome) + '%'
or fa12.nome like '%' + isnull(@faixa,fa12.nome) + '%'
or fa13.nome like '%' + isnull(@faixa,fa13.nome) + '%'
or fa14.nome like '%' + isnull(@faixa,fa14.nome) + '%'
or fa15.nome like '%' + isnull(@faixa,fa15.nome) + '%'
or fa16.nome like '%' + isnull(@faixa,fa16.nome) + '%'
or fa17.nome like '%' + isnull(@faixa,fa17.nome) + '%'
or fa18.nome like '%' + isnull(@faixa,fa18.nome) + '%'
or fa19.nome like '%' + isnull(@faixa,fa19.nome) + '%'
or fa20.nome like '%' + isnull(@faixa,fa20.nome) + '%')
as you see in code above a CD album has 20 slots for songs,
you can search for a album that has a particular song in slot 12 or in slot 6 or whatever slot!!!
you can type exact song name or just a substring(or...like...'%')
you can search for singer, year, kind of music etc.
you can search it all together and see results
it work sweet
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)
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 want to manipulate the receive the table as a parameter in SQL procedure and then want to iterate it to get values and use them in select caluse
i havent done it before if any one can help me out
i want to know how can i get memory occupied by table of a database through query. if some one can provide me a google link where i can find it or the search string to be used for googling it out. it would be of great help.
Also assume memory occupied by the table when no rows present is M , and when row is present the memory occupied is M*1, etc
I have 2 db's for my application one as primary and another as backup.
At any time both will be having same data.
If any DB goes down i will sync it after the DB comes alive. If an entry is present in both the DB's , I now use last updated time to find the latest affected record , but it is causing some problems.
Is there any other way to keep track of the correct record if the entry is present in both the DB's
I take it that the problem is because of the resolution of time in sql server (meaning you can have the same time on multiple rows).
You could use constantly growing surrogate key for each table but not as a key but as a change identification. This would give you unique, growing values for each row so you could easily identify differences between tables based on this column.
We've been storing credit card expiration dates in varchar format as MMYY.
Well, before we would just create a new record and keep the old credit card number instead of deleting it (because in the same table we hold the address and it was a means of keeping customers previous addresses on file), but since we're switching to a new system we need to get rid of the old expired card numbers so we don't get charged for authorizations on cards that we KNOW are expired.
Lastly, we need this to work on a continual basis so when we upload an updated csv file to our processor, we *must* give them the date in the format of MMYY, any updates to what we're doing now need to factor that in... so when we pull that file, we're exporting directly from Enterprise Manager into a text file(csv) ready to import into their system.
So, my question is... how can we...
A. Leave the dates in varchar format and find out what cards expired and delete those records?
B. Convert the varchar dates into datetime dates and delete the old card records without converting to a full date?
P.S. I'd like to add that some dates in these text fields aren't 4 digits. For example: 09/10 is in the database as 910.
use datediff(),dateadd(),getdate() functions will help a lot.
i builted a movie rental application, some rents were up to 3 days, i had a report procedure when triggered counts day between rent day and delivery day or current day.
As you can guess i got a varchar that could be for exmple:
-1) meening i expect movie to be delivered tomorrow
0) meening i expect movie to be delivered today
+3) meening movie 3 days delay
in store) if movie is back to base.
trying to help & get help
Last Visit: 31-Dec-99 18:00 Last Update: 3-Aug-21 14:03