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.

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.

Queries returning Multiple instances of the same record

I am running SQL 2005 and have created a simple database for interfacing with Visual Web Dev 2005 and Visual Studio 2005. I noticed that my applications where returning multiple instances of the same records in the queries. I went back to the SQL server and created a query, and sure enough, I'm getting each record returned 3 times. Where do I start to resolve this issue?DId you have the records already duplicated in the database tables ? Or did you just made a mistake in your join, defining the wrong joined keys. THe best thing would be to provide some information like DDL and some sample data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

My DB and query are much simpler than what you are imagining:

The DB Structure is:

<MemberID, Int,> - Primary Key Autoincrement

<FirstName, nvarchar(30),>

<LastName, nvarchar(30),>

<Salutation, nvarchar(20),>

<MemberType, nvarchar(20),>

<IsNeighbor, tinyint,>

<Title, nvarchar(30),>

<Address, nvarchar(60),>

<Address2, nvarchar(60),>

<City, nvarchar(30),>

<State, nvarchar(2),>

<Zip, nvarchar(9),>

<Phone, nvarchar(10),>

<Email, nvarchar(50),>

<DateJoined, datetime,>

<ExpirationDate, datetime,>

<SubMemberTo, int,>

<Fax, nvarchar(10),>

<Cellphone, nvarchar(10),>

The SELECT query is:

SELECT [MemberID]

,[FirstName]

,[LastName]

,[Salutation]

,[MemberType]

,[IsNeighbor]

,[Title]

,[Address]

,[Address2]

,[City]

,[State]

,[Zip]

,[Phone]

,[Email]

,[DateJoined]

,[ExpirationDate]

,[SubMemberTo]

,[Fax]

,[Cellphone]

FROM [FriendsSQL].[dbo].[Members]

WHERE FirstName = 'ANNE' and LastName = 'REIS'

Without the WHERE clause the query returns the entire DB without duplication, however, when the WHERE clause is included the output is:

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

13 ANNE REIS Anne Reis & Owen Boy Full Member 0 Environmental Coordinator

XXXX E KENILWORTH PL NULL MILWAUKEE WI 53202 4147370000

XXXXX@.PLANET-SAVE.COM 2005-11-01 00:00:00.000 NULL NULL NULL NULL

Notice that the single record is returned 3 times.

|||DOH! You were right. The records were duplicated. Apparently using the SET Insert Unique ON and not having the Primary Key set allowed the duplications. I've cleaned up the mess and I'll try not to shoot off any more toes. Sorry for the bother. I should have caught that one.

Qaurterly Report

Hi everyone,

I'm currently using Visual Web Developer 2005 Express Edition, I have a chart that shows everything The Y axis displays the quantity and X axis shows the month.Well, the X axis I grouped it by month, but i want to set jan-march as q1 and so on.How can I format it, or do so it can display that format?

Thanks ahead of time,
Shuy

It would be easy if you could populate the data from the backend. You could group the data for three months and send the database back to the front end.

|||

If you mean to change the output in some text field based on a column result, you can use IIF to make that change:

= IIF(Fields!Column.Value) = "Quarter1", "Jan-Mar",IIF(Fields!Column.Value) = "Quarter2", "Apr-Jun", IIF(Fields!Column.Value) = "Quarter3", "Jul-Sep", "Oct-Dec")

|||

Thanks so much...just what i needed.