Procs vs Views

[jonwolds]

Hi,

Please could someone tell me in what situation would creating a view would be preferred to creating a stored proc?

 

[gojohnnygogogogo]

one reasaon :
If users need to access the data via some other application.
eg, you can link to a view from ms access or excel.

I also use views for creating reports.

 

[jonwolds]

Cheers,

But could some other application not execute stored procs instead? Using procs would also enable parameters to be passed.

Regarding creating reports, I thought that stored procs would return the data more quickly than a view because they are pre-compiled?

 

[gojohnnygogogogo]

Stored Procs -- use for applications accessing DB's, such as VB or web pages. Create a layer between the DB structure and the applications, force all data access for these applications to use stored procedures.

Views -- use for people who write reports or need to access the data in an ad-hoc manner. Lock down the tables, give rights only to the Views and if possible still try to abstract the DB structure a little so that if it changes, the views will not need to change and existing reports/ad-hoc queries will still work.

but some people use SP's for all access to the data.

you are correct, they are quicker.

 

[jonwolds]

Right I'm kind of getting it but regarding report writing couldn't I still use procs instead of views? i.e Instead of using select on a view, use exec a proc. If I created procs instead of views I could still lock down the tables and grant exec permissions to the procs. Wouldn't this be faster as the procs would be pre-compiled.

Sorry if i'm missing the point.

 

[gojohnnygogogogo]

If you can use SP's for everything then this is the best way to do things.
but sometimes this isn't always possible.
for example, we have users that connect to our data to run their own queries and create their reports, these people don't know how to use stored procedures, so it is easier for a view to be created, they can then query the view.
use SP's all the way if you can.
you're v.lucky if this is the case !

 

[dbomrrsm]

You can also create SP that references a view so that you can allow input of parameters to query the data behind the view.

[blue]DBomrrsm[/blue]