How really store procedure working

am used lot of time , i know the diff between sql query and sp ,

SQL query will be compiled everytime it is executed.
Stored procedures are compiled only once when they are 
executed for the first time.

This is general database question

But one big doubt is ,

For example ,

one dynamic work , that is i pass the userid to SP and sp will 开发者_C百科return the username,password,full details,

So for this scenario the query should execute once again know, so what is the necessary of SP instead of SQL QUERY ,

Please clear this doubt ,

Hi thanks for all your updates,

but i dont want the advantage, comparison ,

just say ,

How sp executing , while we go with dynamic works,

For example ,

if pass userid 10 then sp also read records 10 ,

if i pass 14 then, SP again look the 14 records , see this same work NORMAL SQL QUERY

doing , but on that time execute and fetching ,so why should i go for sp ,

Regards

---

Stored procedures, like the name says, are stored on the database server. They are transmitted to the server and compiled when you create them, and executed when you call them.

Simple SQL queries, on the other hand, are transmitted to the server and compiled each time you use them.

So transmitting of a huge query (instead of a simple "execute procedure" command) and compiling create an overhead which can be avoided by the use of a stored procedure.

MySQL, like other RDBMS, has a query cache. But this avoid only compiling, and only if the query is exactly the same than a previously executed query, which means the cache is not used if you execute 2 times the same query, with different values in a where clause, for example.

---

I see no reason for a stored procedure simply to query for all user details.

Stored procedures are functional code that you execute on the database server. I can think of three reasons why you'd use them:

1. To create an interface for users that hides the schema details from clients.
2. Performance. Extensive calculations on a large data set might be done more efficiently on the database server
3. Sometimes it can be difficult (or impossible, depending on your skill) to express what you think you need in a declarative, set-based language like SQL. That's when some people throw up their hands and write stored procs.

Only 1. would be justifiable from your question. I would recommend sticking with SQL.

UPDATE: The new information you provided still does not justify stored procedures in my opinion. A query that returns 14 records is routine.