| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2008-04-25 : 16:20:20
|
| Hi, I need to code a stored procedure. It will select all the rows from a table. I then need some sort of looping mechanism to filter through all the rows. It is too difficult to handle the filtering with a simple 'WHERE' clause. I will have to do some data manipulation on fields within the rows to determine which ones I could use. I need for the SP to return all the rows which qualify. Could someone provide me with a link and/or some skeleton code to give me somewhere to start on how to code the SP?Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 16:21:24
|
| You need to show us via sample data what you intend to do.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2008-04-25 : 16:32:53
|
| Okay. Here is my SP thus far.ALTER procedure [taser_iuser].[tmptable_query] (@condition_cl varchar(100)) as begin declare @sSQL varchar(2000) set @sSQL='select * from #personnel_table' exec(@sSQL) endI need to loop through this entire table. For each row I need to determine if the individual is >= 18 years of age. Within this table is a date of birth (DOB) field. I don't think the DATEDIFF function inside of a 'WHERE' clause can handle this so I have to figure it out some other way. If they are >= 18 then I need to keep the row. Once looping is complete, I need to return this resultset.Does this help any? Thanks. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-04-25 : 16:48:11
|
| create table #test (name varchar(25), dob datetime)insert into #test values('bill', '1/1/1982')insert into #test values('rick', '1/1/1990')insert into #test values('dave', '1/1/1992')insert into #test values('russ', '1/1/1955')insert into #test values('randy', '1/1/1987')insert into #test values('Van', '1/1/1997')insert into #test values('jim', '1/1/1967')select * from #test where datediff(yyyy,dob,getdate()) >= 18select * from #test where datediff(dd,dob,getdate()) >= (365.25*18) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 16:49:00
|
| fralo, why are you using dynamic SQL?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2008-04-25 : 17:02:43
|
| Tara, my SP actually needs to receive a dynamically created 'WHERE' clause like such:set @sSQL='select * from #temp_table' +@condition_clI simply took it out of my original post on this forum so as to eliminate it as a factor for discussion. But yes, to be honest, it is something I will have to take into consideration. The where clause generated depends on user input in a .NET application I'm writing. They can enter one to many textboxes. Based on which ones they choose will determinely what the clause looks like. So it needed to be dynamic.Hope I'm making sense.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2008-04-25 : 17:25:37
|
| Sorry for the duplicate post. It's just that whenever I did the original post I thought that I could handle my issue with an innovative 'WHERE' clause, and not using a gigantic function as I learned. So I decided to start a new thread. Apologies.So Tara, thanks for your help. But in addition to the dynamic sql article, I still could use some help with the structural format of how my SP should look to handle my original request. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-25 : 17:37:12
|
| Why does it matter how big the function is? You don't need to write it or understand it, just create it in your database and then use it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2008-04-28 : 11:36:55
|
| I have been testing with Van's suggestion and it seems to be what I'm looking for.select * from #test where datediff(dd,dob,getdate()) >= (365.25*18)By testing on the day level of the date I have received accurate results. Thanks to you all for you suggestions. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
Looping in stored procedure