TcM

Stored Procedures Will Save You Time
Well, just to let you guys know, I have more experience in MS SQL than MySQL… so maybe some stuff that I’m going to mention here might not be the same in MySQL… so don’t blame me.
And you might ask, why do you use an MS product? Simple. School… so again don’t blame me.
Anyways right now I’m very busy with creating / designing a very advanced software that uses databases for my Final Software Project, which will pretty much determine my future as a programmer, but I found sometime to share this with you in my blog. I’m only going to discuss the theory side of stored procedures, I’m not going to make this blog a tutorial on how to create them etc, I think there are many tutorials out there that can show you how to do this way better than me.

What are stored procedures?
Well, if you are reading this blog most probably you do have some knowledge of programming, so pretty much a stored procedure is just like a method, it is a chunk of code that instead of copy/pasting it every time, you just make that chunk of code into a method and when you need it you just call that method. And just like a method, stored procedures, accept parameters and these parameters can be used within the chunk of code, because most probably the stored procedure will execute the same code, but the result will not always be the same, because sometimes you might need to search for client id 1 and maybe another time you will need to search for client id 2, the coding is the same but not the result.

Why use them?
Well, mostly (personally) I downloaded and tried tons of software / scripts but none of them used these, mostly they had the SQL statement in a method and they call the method, instead of the stored procedure. Well, that is a good idea, but personally, I don’t find that coding very clean. It’s better to have all those repetitive SQL statements stored as procedures on the SQL server and then you call it neatly from the program by just calling the stored procedure and forwarding the parameters… which pretty much will not require and SQL statements in the software.
Personally I tried both, I used SQL in the programming and used stored procedures, I must say that you will find it a lot easier and less confusing when using stored procedures. It will take you less time to develop applications that way and you will have fewer errors. And I’m not sure if it increases security too , since you will not have any SQL statements in the software itself, but I can’t really say that… but maybe someone can tell us in a comment.

Why this blog post?
Well, I thought I’d share this, although most probably you will prefer to use SQL within the software, and most people (beginners* like me) would not even know that these stored procedures exist… and that they can save them a lot of time and effort and less bugs. So basically I just wanted to get this information out for these people… because beginners* like me can save a lot of time and use that extra time to do some other activities.
*beginners = does not mean they are new to programming, just that they are new to databases.