August 30, 2008 04:38am pdt

1. NickUpson 55,648
2. MicheleM... 6,293
3. imfallible 6,000
4. kacor 5,780
5. ee_rlee 4,000
6. gskoczylas 3,914
7. angelIII 2,000
7. metvacetz 2,000
7. sas13 2,000
7. lssaul 2,000
7. dvonderb... 2,000
7. jaime_oliv... 2,000
7. Arthur_... 2,000
7. Fernando... 2,000
15. talker2004 1,800
16. hielo 1,600
17. rionroc 1,500
17. kaylanreilor 1,500
19. dtodd 1,400
20. TheLearn... 1,250
21. owensleft... 1,000
21. Ustin19 1,000
21. LPurvis 1,000
24. CEHJ 900
25. grendel777 750
25. mwecom... 750
25. Limbeck 750
25. developm... 750

1. NickUpson 319,497
2. kacor 51,844
3. BAlexand... 39,502
4. hobbit72 27,290
5. Stummel 15,838
6. Limbeck 13,475
7. idt 12,791
8. Trekker72 9,840
9. phuocnh 7,650
10. vowca 6,618
11. Deesy 6,310
12. MicheleM... 6,293
13. tkalchev 6,201
14. ziolko 6,200
15. imfallible 6,000
16. YodaMage 5,982
17. thegroup 5,672
18. ezraa 4,522
19. chart11... 4,148
20. ee_rlee 4,000
21. gskoczylas 3,914
22. JDSkinner 3,664
23. angelIII 3,600
24. jonneve 3,500
25. mlmcc 3,200
25. unordained 3,200

04.28.2008 at 10:24AM PDT, ID: 23359449

	[x] Attachment Details

Need help writing stored proc in Firebird DB

Asked by Nugs in Interbase / Firebird Database

This is more of a request for help than a specific question. I have zero experience writing stored procedures in Firebird but have run into a situation where it might be best that I return this type of data using a stored proc rather than the current method I am using.

Basically what I am requiring is a count of records for each day in a date range. So for example if a user puts in 4/25/08 to 4/28/08, currently my C# code will loop through each day of the range and request a count from the database. So we are talking about a separate query for each day of that is requested& This is done with the with the attached code snippet.

This method returns an ArrayList of my counts for any table I need and any date range I need& But performance wise it stinks. And these queries can range over 90 days or 90 counts&

SO& this is where I need help. I would like to have a stored proc that will do a query like this for me. I would like a stored proc that I could run that would give me a TABLE of counts in sequence of the days requested. It must return a table as my C# code will need to loop through it and would not know the original sequence. It would also be nice if this stored proce was flexable enough to accept any table name and field names for the date comparison.

Would anyone be kind enough to help me with this? I am not really even sure the work involved or anything on the subject really.

Much appreciated

Nugs
Start Free Trial

         
             public ArrayList GetRecordCounts(string TableName, string DateField, int NumberOfDays, DateTime StartingFrom)
        {
            ClassLib.Database_Interface.DBQueries DbQuery = new ClassLib.Database_Interface.DBQueries();
            ArrayList RecordCounts = new ArrayList();
 
            for (int i = 1; i <= NumberOfDays; i++)
            {
                string Query = "SELECT Count(*) FROM " + TableName + " WHERE " + DateField + " = '" + StartingFrom.AddDays(i * -1).ToShortDateString() + "'";
                RecordCounts.Add(DbQuery.RunSelect(Query.ToString()).Rows[0]["COUNT"].ToString());
            }
 
            return RecordCounts;
        }

Keywords: Need help writing stored proc in Firebir…

	Title
1	simple query with firebird
2	Which database to use (mysql or fire…
3	Delphi and Firebird
4	Firebird Advice and discussion
5	Remote Firebird database server instal…
6	Import data

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_2_20070628

Need help writing stored proc in Firebird DB

Asked by Nugs in Interbase / Firebird Database

About this solution