What are the uses of Cursor,Function,Stored Procedure,Trriger.

Posted by Sapna under Sql Server on 2/18/2010 | Views : 14241 | Status : [Member] | Replies : 6
I am currently handling few small projects n VB.Net as front end and Sql Server as back end.I want to know when should we use Stored Procedures,when to use Cursors,when to use Functions and when to use triggers.
Please explain me with simple example how can we use all this stuff.




Responses

Posted by: Abhi2434 on: 2/18/2010 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down
Procedure : They are a pl/sql block which you can use to define logic around the data before/after operating into it. We generally call Stored Procedure to do most of the operation to have better grip over our data. Also using Stored Proc it gives an additional block where you can easily change your logic without deploying the application.

Function : These are small code blocks that gets some input from outside world and returns one output. Functions are used to call from the storedproc or any sql objects. We dont call Functions directly form .NET objects

Cursor : This is actually a temporary table created during execution of a pl/sql body. This is used to fetch records from a select query, hence helps in manipulating data.

Trigger: They are pl/sql blocks that are called automatically when some DML operation is performed. So if you want your block to be executed automatically use trigger.


I hope you like my explanation.

www.abhisheksur.com

Sapna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
Trigger

1)When you create a trigger you have to identify event and action of your trigger
2)A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. .
3) A trigger is triggered automatically.
4)It does not pass parameters

Stored procedure

1)Explicitly executed by invoking call to SP
2)you can not join SP
3)We can call a stored procedure from front
4)Stored procedure can take the input parameters

Function

1)can be used with Select statement
2)Not returning output parameter but returns Table variables
3) Cannot have transaction within function
4)Cannot be used to change server configuration

Sapna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/13/2012 [Member] Starter | Points: 25

Up
0
Down
Nice explanation Ms.Muhsinathk

Thanks & Regards
Hari

Sapna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
Thanks Mr.Hariinakoti

Sapna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/14/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi Ms.Sapna

Please Mark as Answer if it helpful to you..That helps other who search the same...

Sapna, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response