Stored Procedures [Resolved]

[ober0330]

This isn't completely a PHP question, but it will end as one.

Why would you use Stored Procedures? Is it mainly for queries that you run all the time? If so, do you just create them like you would any other query?

And I know you can run them with mssql_execute(), but what do you call inside the brackets? The name of the SP?

Thanks for your help.

[The Hobo]

What are Store Procedures?

[ober0330]

They are chunks of code that you write at the server, and instead of calling the same piece of code a lot of times from a page, you call it at the server, and therefore it's faster and optomized. (optomized because the server parses the code and optomizes it on the first run, therefore making it faster for any subsequent runs)

[The Hobo]

Sounds like gibberish, but alright.

[ober0330]

If there is a piece of code that you always run, you store it on the server instead of in your page, so that the server runs it faster and spits out the results faster. It's a speed thing.

[The Hobo]

How do you "store" code on the server?

All my php files are ON the server, so they are stored on the server.

You're not explaining this very well.

[ober0330]

**** man... look it up. Yeah, you're storing PHP files that include scripts. No crap.

I'm talking about scripts stored in the actual database. MS SQL server to be exact. Under each database on the server, there is a folder or portion of the server containing scripts which are used over and over and over. Instead of writing out the script in a PHP file, you store it in the database and make a call to it... the database processes the results and sends them back... all that is optomized because the database has control over more than it would if you had called the script from a PHP file.

I just want to know how to call it and an example of a use where someone is actually using it.

[The Hobo]

LOL

[ober0330]

Are you high or something??

[The Hobo]

Originally posted by ober5861
Are you high or something??

No, I just love when people act like you did in the opening of that last post.

Don't ever try to teach a class or anything.

[ober0330]

I'm sorry. I'm just getting a little frustrated. I just find it very hard to believe that you've never heard of Stored Procedures. It's a fairly basic optomization technique. I had heard of it before, but never actually used it. That's why I'm here... not to explain it, to get help.

[The Hobo]

Well, I guess I must be stupid having never heard of it.

But this is an open forum. Just because you started by asking a question doesn't mean that others can't answer questions as well.

I asked you what they were. After you tell me, I could have been interested enough to actually go learn more AND find the answer to your question.

If you just want a straight answer, have fun waiting for it.

Sorry to inconvenience you.

[ober0330]

OK... let's start over:

What did you not understand about my explanation of what Stored Procedures are?

And I'm sorry if I can't explain them very well... I think if I knew everything about them, I wouldn't be asking this question. I'm new to them too (in terms of using them) and I was hoping someone could even better explain them to me.

[kows]

I didn't know what stored procedures were either, never even heard of them.. well, might've literally heard of them, but have no knowledge about them.. so, I avoided this topic, and now that I see it has a bunch of replies, I thought I'd learn a bit about them.. guess not.

I must be an idiot too.

as Hobo asked, how do you store them? If you store them, are you storing PHP commands? Or are you storing SQL commands? hmm.. I'd go look around for an answer, but with a connection that acts like a 28.8k modem until the start of Feburary, it's hard to get around.

[The Hobo]

Originally posted by ober5861
What did you not understand about my explanation of what Stored Procedures are?

It just seems that it'd take longer if you have to query the function from a database and then run it. It just all seems confusing to me, but I'll wait and see if someone has an answer for you or if they can explain it better.

[ober0330]

OK, here's what I know about them:

In the enterprise manager (software that allows you to view different SQL servers and manage them) that comes with MS SQL Server, there is a list of things you can use under each database on that particular server:

-Diagrams
-Tables
-Views
-Stored Procedures
-Users
-Roles
-Rules
etc.....

Under that (Stored Procedures) is a list of procedures that can be run. In the properties of each is SQL commands, NOT PHP. The purpose is to store queries (some contain logic but I'm not sure what language it is in... possibly C).

Then all you have to do is make a call to that query from your PHP code (or so I believe) or from another routine that runs on the server (during backups, data transformation, replication, etc.).

That's where I need help. How do you call them? I haven't seen any documentation and I've done a fair amount of searching.

Does this help to explain it more?

[techgnome]

Stored Procedures deal with DATABASES.
They are reusable SQL code statements, typically with parameters of some sort, that can be called by other processes (VB, C++, PHP, etc)
SPs typicaly reside in the DATABASE SERVER, has nothing to do with where PHP files are. I think ober cloded the waters a little by posting this in the PHP area (Although understandably why).

Now, for ober's question.... what do you think? When you execute an SP from ADO, don't you give it the name of the SP to run?

The reason you may be finding documentation lacking, is that as of yet, MySQL still doesn't support SPs. Unless something has changed in the last couple of days. It isn't expected to support them until v5 comes out (allegedly any day now) --- hrm.. a quick check on the site shows it's in "Preview"... hrm....

TG

[ober0330]

OK, well, never having called one using ADO, I don't know how the call is made.

Hence back to my origional question..... how do I make a call to one in PHP? Even php.net is cloudy on it.

And keep in mind that I'm using MS SQL Server, not MySQL.

[The Hobo]

http://www.php.net/manual/en/function.mssql-execute.php

Pardon me if you've looked at this alread, but it seems like the comments on this page may hold some sort of an answer.

[CornedBee]

mssql_bind for binding arguments, then mssql_execute.

MySQL will support stored procedures starting with version 5.0, which is currently in alpha stage.

[ober0330]

I had looked through that before, but everything I tried didn't seem to work. But I played around with some of what they said again and I did get something to work:

$result = mssql_query("GrabCellNames");
$row = mssql_fetch_array($result);
echo $row[0];