QUERIES VS STOREDPROCEDURES

I use visual studio 2005 standard edition and sql express 2005.I would like to know if there is any difference using queries(designed in dataset xsd) or stored procedures.

Also I am having the following problem :Whenever I make a change on a table in my database (add columns etc) and use the wizard to retrieve the changes i loose all the queries for that table (I have to write them again).Am i doing something wrong?

Thanks in advance!!!

There is quite a bit of difference between VS queries and SQL Server Stored Procedures.

First, Stored Procedures are actually stored in SQL Server. They don't just 'vanish' -as you are discovering happens to your VS queries. (If you make changes to the tables that cause the stored procedures to fail, they will still be there -just error when executed (until repaired.)

Stored procedures, when called, are compiled and an execution plan is prepared on SQL Server. That execution plan can be reused the next time the stored procedure is called -saving time.

Security can be applied to stored procedures, allowing or denying the 'right' to use them.

Developers don't have to know where the stored procedures are getting their data, and the application doesn't have to be changed when the database changes -just change the stored procedures.

And the list goes on...

|||

Stored procedures are easier to maintain than in-line queries. They are more secure than in-line queries, since they are less vulnerable to SQL Injection attacks. They can also reduce network traffic (incoming to the DB server).

As long as you don't change the interface of a stored procedure (name, parameters, and return values). This will insulate you from the problems you are seeing now when you make table changes.

|||

Hi,

If you seperate the database queries from your application codes by using stored procedures, it will be easier to modify the sql queries for purposes of improving performance, updating queries, etc. Otherwise you should make those changes in your application.

Eralper

http://www.kodyaz.com

|||

Thank you!

I can see now the benefits of stored procedures.