Database Discussion - SQL and stored procedures

Print Page | Close Window

SQL and stored procedures

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=9904
Printed Date: 30 March 2026 at 5:58am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL and stored procedures

Posted By: Semikolon
Subject: SQL and stored procedures
Date Posted: 17 February 2004 at 4:07pm

yo.. when using simple SQL statements like this


strSQL = "SELECT " & strDbTable & "SubCategories.* FROM " & strDbTable & "SubCategories "
strSQL = strSQL & "WHERE " & strDbTable & "SubCategories.Category_ID = " & lngCategoryID & " "
strSQL = strSQL & "ORDER BY " & strDbTable & "SubCategories.Sub_category_name DESC"

is there any need for Stored Procedures then? does it make any difference on performance (and other things)?

Replies:

Posted By: Gullanian
Date Posted: 17 February 2004 at 4:11pm

Stored procedures (SP) are more secure (dont ask me why) and they execute faster on the database. SP syntax is very simple to learn, and just adds that edge to your software. Not necessary, probably wont even notice a difference with them but might as well have them imo.

Posted By: Gullanian
Date Posted: 17 February 2004 at 4:13pm

Some info on SP: http://www.4guysfromrolla.com/webtech/sqlguru/q120899-2.shtml - http://www.4guysfromrolla.com/webtech/sqlguru/q120899-2.shtm l

Posted By: Mart
Date Posted: 17 February 2004 at 4:19pm

SP's are precompiled so they perform better, all you have to do is

CREATE PROCEDURE [name]

(

[params]

)

[query]

Posted By: michael
Date Posted: 17 February 2004 at 4:48pm

The main reason that they are fast is not necessarily because they are pre-compiled but because SQL has a and execution plan for it which may improve performance.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Semikolon
Date Posted: 17 February 2004 at 4:54pm

(I know how to use Stored Procedures)

so theres no BIG differences between SQL query in the code and Stored Procedures on queries like that?

the reason why i ask this is that its twice as much work when changing an SQL statement if making a script for both access and SQL server if i use stored procedures

for me it sounds ineffective with stored procedures (first call them from the script, then query the table(s) and send it back to the script instead of a direct call to the table(s)) but that may be me...

but thanks for ya help guys i appreciate it

Posted By: Mart
Date Posted: 17 February 2004 at 4:59pm

For long quries like that you should use an sp, for one reason it will make your code look neater. It will also have performance advantages beacuse their precompiled and as Michael said:

michael wrote:

SQL has a and execution plan for it which may improve performance.