March 4, 2013 at 2:08 am
i get confused where to use function , store procedure and view.plz help me
😉
March 4, 2013 at 3:14 am
vahid.arr (3/4/2013)
i get confused where to use function , store procedure and view.plz help me😉
What do you mean by "confused"? What exactly troubles you?
View - it's kind of virtual table which you define using SELECT query.
Stored Procedure - is kind of programmatic block. It can perform some task and may return result in a way of integer result code, output parameters and recordsets.
Function - is program block which can return operation result (scalar or table).
March 4, 2013 at 5:32 am
1-for example in insert , delete , update i must use function or store procedure? could u give me example for store procedure and function
2- if our select has parameter it also must use view?????
March 4, 2013 at 6:56 am
vahid.arr (3/4/2013)
1-for example in insert , delete , update i must use function or store procedure? could u give me example for store procedure and function2- if our select has parameter it also must use view?????
"must" is incorrect word here.
1. You cannot use user defined function in SQL Server to insert, update or delete. You can use stored procedure in order to do so (not must, as you can use in-line sql from a client application - not very great design, but possible). Also, there are ORM ways to use databases within applications (eg. nHibernate) which also do need stored procedure for data operations.
2. You can use view if you want - it's not "must", as you can access tables directly in your query and filter results as required using WHERE clause.
Examples
for function: http://msdn.microsoft.com/en-us/library/ms186755.aspx
for stored procedure: http://msdn.microsoft.com/en-us/library/ms187926.aspx
March 8, 2013 at 12:52 am
ok i got it thanks alot
June 21, 2013 at 12:44 pm
A stored procedure and a view will both return you a result set. A stored procedure can accept parameters, such as start date and end date... whereas a view cannot.
A view is one statement in sql, where as in a stored procedure you can have many statements. Such would be when you first want to update a table then pull the results (two statements) and all based on the values given to the parameters given to the procedure. You don't actually need to have a procedure accept parameters but that's usually how they are used.
A function is just like a function like in any other language, ... give it inputs and it gives an output in the traditional sense (like an integer). SQL server can give you a table as output, think of it as a 3 dimensional array.
You will usually use functions when you want to automate a granular process and other bits of code in the company would implement this,, such as how you round your dollar figures in the final output of a view or stored procedure.
I hope this helps.
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply