Which is best stored procedure or simple query?

Started by andrewneil, 10-18-2012, 12:36:10

Previous topic - Next topic

andrewneilTopic starter

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


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
ERP Software [nofollow]
Payroll Software [nofollow]
  •  


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.
newbielink:http://krishika.com[/url [nonactive]
  •  

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