View vs Stored Procedure From loading view only form data

I am building a simple Window Forms application that pulls data from a remote SQL DB.

I have been using SQLConnection with SQL data Adapter and SQL Commands to pull data from the DB via stored procedures instead of building SQL statements in the application.

Some of these SP are just select all statements, so i was thinking, it might be easier to use a view...

however, you cannot use a view with a SQL command.

I have been avoiding doing direct data binding to the form, is there a reason why I would be better off doing data binding to the form and using a SQL view rather than using the SP?

The view and the SP are running identical select statements.
PitSquadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
As a DBA I thank you for not including SQL statements in your application.

You can certainly use a view with a SQL Command.  You treat it exactly as you would a table.  The choice of a view vs a stored procedure IMO comes down to how complex your query is.  If you have or expect to add complex logic in your query then I would opt for a stored procedure.

As far as the data binding I am a little rusty on my dev skills so hopefully someone else can jump in with a suggestion.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Some of these SP are just select all statements
It is not good programming practice to do a SELECT *, especially within a Stored Procedure if there is further code that expects the table to be a certain schema, as if the schema changes it might cause the SP to generate an error.

>is there a reason why ... using a SQL view rather than using the SP?
Nothing immediately comes to mind, as both are compiled so I don't believe there would be a performance difference.  Maybe there is a security issue in play where it's easier securing a view then a SP.  Might just be a judgement call.
0
Jacques Bourgeois (James Burger)PresidentCommented:
It depends on the complexity of the query, but the stored procedure offers a few advantages:

- The first one is obvious, they do a lot more

- You can pass parameters

- I have been in that field of work long enough to always put maintenance as a priority when I design something. New needs or problems might appear in the future that will require a more complex operations that cannot be done in a view. If you have many applications that use the view, they will probably have to be rewritten in order to access the new format. If the SQL is already in a stored procedure you probably won't have to rewrite the code unless you change the parameters list.

I don't use views that much, so I might miss a few more.

Personnally, I use views mostly as helpers during development. In the real world, specially because of my third point, I always use stored procedures. When I use a view "for real", it's usually when I need a complex joint that would have to be recreated in many stored procedures. In such a case the procedures will work with the view, and if I need to make changes to the joints or add fields, I simply need to make them in the view.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.