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 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.

more