Which is best stored procedure or simple query?

[andrewneil]

Please tell me which is best stored procedure or simple query and also tell me benefits of best one?

[pstpl1]

As

Begin
If @p3 is null

Select * from table where field1=@p1 and field2=@p2

Else
Select * from table where field1=@p1 and field2=@p2 and field3=@p3
End

[krishikaseo1]

Stored Procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most famillar with SQL Server I will use that as my samples.

[MarcoLeitner]

1.Stored procedure are stored queries in a database . They are pre compiled. when we request database to execute stored procedure , sql server already has the plan to execute stored procedure. while in case of simple query, it needs to create execution plan on runtime.

2. Stored procedure are pre compiled and optimised which means query engine can execute them more rapidly where as in case of simple query code must be parsed, compiled and optimised at run time which takes more time.

[Ghazala]

Hello All,

The stored procedure is a set of PL/SQL statements written inside a named block. We use stored procedures in Sql Server to perform the very basic operations on database tables.

Thanks