Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedures : When and when not
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 11/29/2001 :  12:13:49  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
And if you find that the procedures are performing slow it's probably the coders dumb ass fault for not knowing enough T-SQL!!! ... heh heh I had a few SPROCS that took about 40-90 seconds to run... and about 250 lines of selects and joins gawde what a nightmare one night I found one single trick... ONE SINGLE TRICK USING CASES and it turned the code into 1-3 seconds executions and only 50 lines of code :-0 I didn't want to believe it but it was BAD CODE ... heh heh ... live and learn ...

- Onamuji
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 11/29/2001 :  14:38:38  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Onamuji,

Thanks for the input! I probably should do more reading on this Connection topic on some ASP sites. I got in the habit from having several pages where I had multiple queries going on to populate a number of select boxes. These were done in ASP functions, just passing the connection object to be re-used. Needs more research...

On the topic of cached execution plans I don't know if there is any validity to this, but I was once told that this was a factor in Pre-version 7 SQL Server, but that SQL 7 resolved this issue.

And now, my story about why stored procedures are SO GREAT! A few months ago I build an Events Calendar that had a search page with 7 different parameters, any one or more may be filled in. I built a somewhat lengthy Dynamic SQL statement to handle this. Initially I was building this in the ASP Code, but after a few parameters were coded, I moved it into a stored procedure. Well guess what happened? After the initial project was into beta testing, some new project requirements came up, which essentially mandated that there be 15 other similar, but not identical search pages. Usually containing a sub-set of the first page's parameters. So what happened? It was EASY because I just had each page call the same stored procedure, and voila! it worked beautifully! All my search code is in one place. No having to change 15 different ASP pages whenever the requirements change. Which they did about 6 months after implementation, we decided to change the SORT ORDER of the resultset. And again... EASY because it was all in one place.

That's my story, and I'm stickin' to it!

-------------------
It's a SQL thing...
Go to Top of Page

kensai
Posting Yak Master

172 Posts

Posted - 11/29/2001 :  21:08:53  Show Profile  Reply with Quote
Onamuji I was just thinking to use the stored procedures the way you've mentioned, with using Recordset. Ever since I've started coding ASP I always used Recordset for database connection. I never ever used Connection and I'm much more convenient with Recordset.

Let me tell you what I have in my mind, and please let me know your opinions on it.

Think about the main page of Snitz. Although I didn't looked to it's code but I think it makes several connection to several tables. I assume they made normalization on the database to a degree(3rd perhaps?). It displays categories(connect categories table). It lists forums(connect forum table). It displays forum descriptions (I think it's on forum table too). It displays topics and posts(again on the forum table?). It displays moderators(connect moderators table). It displays statistics like total members(connect statistics table?). I'm thinking to shrink to a single command : objRS.Open "proc_DisplayMainPage" . This procedure will do that all work and return the result as a recordset. I don't quite a grasp over it but it's been awhile since I' worked stored procedures last, I started to read T-SQL part of my SQL Server book again. I'm also thinking to use disconnected connection.

So what do you think about this?

Go to Top of Page

smartlizard
Starting Member

USA
24 Posts

Posted - 11/30/2001 :  00:23:05  Show Profile  Visit smartlizard's Homepage  Reply with Quote
When refering to SP what is Dynamic SQL? Can you show me a SP with Dynamic SQL?

Thanks

Ron Sell
http://smartLIZARD.com
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/30/2001 :  00:41:52  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi Ron

There are a coulple of links in the FAQ



Damian
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 11/30/2001 :  13:13:41  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
Personally I use all ASP in my applications since I code on IIS 5 it's quasi ok since at most my application gets hammered by 50 people at a single instant so no big deal.

But how I feel personally (I do the above because of time frames) I would write a Data Access Layer(DAL) component that would handle all interaction with a database.

Reading the Duwamish Online articles and the Fitch Mathers sample applications can help you all learn the better coding standards. Now each sample has it's ups and downs. I've combined all the *good* things I've learned into a simple codology that works really well for me at least.

The Snitz forum code was made to work with multiple databases that's the only reason I can assume why it uses ASP based SQL strings and not SPROCS ...

- Onamuji
Go to Top of Page

kensai
Posting Yak Master

172 Posts

Posted - 12/02/2001 :  14:30:00  Show Profile  Reply with Quote
Thanks for the info Onamuji

Go to Top of Page

bgc123
Starting Member

5 Posts

Posted - 12/09/2001 :  14:44:07  Show Profile  Reply with Quote
I'm another stored poc proponent. Even if the site is "small," the benefits of using stored procs are too great to ignore. Safer, faster (due to the query optimizer), far more flexible, etc. And MUCH easier to debug/modify than ASP.

RE: Insert/update/delete and not needing any results. Take a look at using adExecuteNoRecords. That way, you are explicitly declaring no results need to be returned.

Good example is here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp

More reasons to use adExecuteNoRecords:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcmdexecute.asp

From MS: If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset.

Kensai: As far as ASP/SQL-based forums, I would also recommend IdealBB - http://www.idealbb.com/. I downloaded his free beta version and the code looks pretty tight (lots of sps).

Good luck,

Bruce

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000