Stored-Procedure vs Views

Question

Stored-Procedure vs Views

priyaranjan Mohanty

SSC Enthusiast

Points: 190
More actions
September 26, 2006 at 11:25 pm

#91789

Hi All,
On some very rare instances , Stored-procedures can be replaced with Views.
My question is on those scenarios where the requirement can be fullfilled by either using Views or Storedprocedure , using which among these 2 will be the best approach & why.
Thanks in Advance
pzmohanty

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It all depends what the requirement is. A view can be thought of as a virtual table. It can be queried, joined to other tables or views and, under some circumstances, updated. Stored procedures can return a result set, the same as a view, but if you wanted to query or join it then you would need to put the results in a temp table. Stored procedures can do many things that views cannot, such as sending e-mails and updating all data. Stored procedures are arguably more secure, since they do not expose the structure of the data from the caller.

The answer is probably to use a mix of the two as appropriate. If you have any further questions, then please tell us what you are trying to do, and we should be able to help.

John

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

GREAT answer... I'd also add that I'd avoid views of views (can become a little memory intensive, more difficult to trouble shoot, and if written incorrectly, will need one view to materialize in memory before the other view can use it) and, if you're only going to do it once, don't use a view.

I also do a simple test to see if a view is written properly... determine what looks to be able to be used as a primary key from the view and write a simple SELECT to return one row using that column or columns... if it doesn't come back right away, you might want to take the time to find out what the problem in the view is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

priyaranjan Mohanty SSC Enthusiast Points: 190 More actions · Answer 3

Thanks John & Jeff ,

I got the point you are trying to convey.

really appreciate your prompt response.

thanks again