stored procedure

Im not sure if this the correct forum but anyway...

i'm am rewriting some update code which is used to update several tables in my sql server 2000 database, and am considering using stored procedure as I have read they help to improve performance (and I might require introduce some more security which can be done using stored procedure...)

I am very new to programming…..

1)when writing the sp can I write 1 stored procedure to update all tables, or is that not possible since some parameters have differernt names in the different tables??? And should all tables be updated using stored procedure..

Quote:

---

Originally Posted by pame1la

Im not sure if this the correct forum but anyway...

i'm am rewriting some update code which is used to update several tables in my sql server 2000 database, and am considering using stored procedure as I have read they help to improve performance (and I might require introduce some more security which can be done using stored procedure...)

I am very new to programming…..

1)when writing the sp can I write 1 stored procedure to update all tables, or is that not possible since some parameters have differernt names in the different tables??? And should all tables be updated using stored procedure..

---

Well... Always using Stored Procedure to perform database operations, is not a rule of thumb....

Stored Procedures are better in performance because the code within them is already compiled and so the time of Database Engine for compiling the query again and again is saved...

and obviously you shouldn't use one stored procedure to update all the tables... this is against the structured approach..

Use Stored Procedure when there is a query or set of database commands which are frequently being called by front-end...

About me... i don't use Stored Procedure to insert data into tables..one of the normal case when i use them is when i need to validate some user input against the values in one or more tables in the database..

Quote:

---

Originally Posted by moinkhan

About me... i don't use Stored Procedure to insert data into tables..

---

Could you share why, is it a personal decision? Or have you encountered some problems when using stored procedures to insert data? This is really intriguing because I have always thought that using Stored Procedures for your data manipulation is the best way to go.....

Quote:

---

Originally Posted by dee-u

Could you share why, is it a personal decision? Or have you encountered some problems when using stored procedures to insert data? This is really intriguing because I have always thought that using Stored Procedures for your data manipulation is the best way to go.....

---

So would you advise using a stored proc when updating any table (even when updated few columns??)

This might give you a better answer..... :thumb:

Quote:

---

Originally Posted by dee-u

This might give you a better answer..... :thumb:

---

Thanks, that document was well useful! I think i will be changing the whole update stage and create stored proc 4 each table needed to b updated :D

Thanks 4 ur help!

We do everything with SPROCS - no direct table access is granted to any users at all. They only have EXECUTE rights on SPROCS.

That's the security benefit of SPROCS over direct table access.

We use SPROCS to UPDATE and INSERT data all the time - from rows in FLEXGRID's. We call the SPROC once for each row and do the INSERT.

If the row has data that belongs in more than one table, then that single SPROC will update all the tables. Encapsulation rules kind of dicate that - don't they? If you have one SPROC update one table and another SPROC update a sister or child table, you run the risk of the second SPROC failing and the transaction/rollback feature of the database will have been subverted by you using two SPROCS.

Quote:

---

Originally Posted by szlamany

We do everything with SPROCS - no direct table access is granted to any users at all. They only have EXECUTE rights on SPROCS.

That's the security benefit of SPROCS over direct table access.

We use SPROCS to UPDATE and INSERT data all the time - from rows in FLEXGRID's. We call the SPROC once for each row and do the INSERT.

If the row has data that belongs in more than one table, then that single SPROC will update all the tables. Encapsulation rules kind of dicate that - don't they? If you have one SPROC update one table and another SPROC update a sister or child table, you run the risk of the second SPROC failing and the transaction/rollback feature of the database will have been subverted by you using two SPROCS.

---

Thats what I considered previously as the data in datagrid is used to update several tables in 2 different dbases, but then I thought if I wrote seperate s proc i could wrap them into 1 transaction, since this means i could use these stored proc at other times. Also moinkhan (1st reply to this thread) said:
"and obviously you shouldn't use one stored procedure to update all the tables... this is against the structured approach.. "
so I thought it wasnt a done thing??????

Quote:

---

Originally Posted by pame1la

Thats what I considered previously as the data in datagrid is used to update several tables in 2 different dbases, but then I thought if I wrote seperate s proc i could wrap them into 1 transaction, since this means i could use these stored proc at other times. Also moinkhan (1st reply to this thread) said:
"and obviously you shouldn't use one stored procedure to update all the tables... this is against the structured approach.. "
so I thought it wasnt a done thing??????

---

Using one SPROC to update all tables is a very different statement then using a single SPROC to update a pair of tables, or three tables, all from one grid row.

In my opinion, updating the DATABASE from a GRIDROW is a piece of business logic that should be together.

But I must point out that we do not have a true n-tier application at our shop - we are using SPROCS as the data-tier (this is far from traditional and I've had many threads where I've debated it).

Our VB client is completely blind to what it's maintaining.

For example - we populate a FORM and GRID with data from an INQUIRE sproc for our USER/ROLE maintenance form.

When the SAVE button is clicked we call a SPROC that has these parameters:

Code:

---

Create Procedure frmUserRolesFMS_update
        @PassConnId int
        ,@RetStat int Output
        ,@RetText varchar(100) Output
        ,@RetMode int Output
        ,@RetGrid int Output
        ,@RetExtra varchar(100) Output
        ,@PassMode int
        ,@PassExtra varchar(10)
        ,@PassTest varchar(10)
        ,@UserId int
        ,@Name varchar(50)
        ,@UserName varchar(50)
        ,@RowData int
        ,@Col0 varchar(30)
        ,@Col1 varchar(1)
        ,@Col2 varchar(1)
        ,@Col3 varchar(250)
        ,@Col4 int
        ,@Col5 int
as

---

frmUserRolesFMS is the NAME of the VB FORM that SAVE was clicked on. _UPDATE indicates that this SPROC is used when SAVE is clicked. The VB app finds the SPROC by looking for this NAME - this is dynamic at runtime - not hardwired into the VB code.

The first 9 parameters are standard in all our SPROCS - so the VB client can communicate and be controlled by the SPROC.

Then the rest of the parameters are related to the FORM. @UserID, @Name and @UserName are text boxes on the FORM. @Colxx indicates columns in a flex grid. @RowData is related to the .RowData values of the flex grid row (we set it to 1 if "existing", 2 if "dirty", 4 if "complete" - stuff like that).

This SPROC is evaluated at run-time to see what "parameters" are existing and the VB client attaches the proper values from the FORM objects and calls the UPDATE SPROC. One call for each ROW.

So, in our case, we would want this one SPROC to update the USER and USERROLE table. It's basically our data tier - but written in T-SQL as opposed to VB or some other language - and it doesn't use COM for communication - it uses ADO.

Sorry for the long explanation - but I wanted to show you that there are many ways to approach data manipulation.

Our shop runs things in a similar manner as szlamany. This is how I would put it. Stored Procs that do updates should work on a LOGICAL DATA ITEM. Meaning, that the data that is being updated is treated as being similar in some way, as in szlamany example, the user and userroles. The actual data may be spread over multiple tables, but it is one related Data Item. Here's an example in our case. Our app is a CRM (Customer Relations Management) system. An account in our system can have one or more contacts, but only one of them can be the primary contact. From the user's perspective, when they view the account, they see the contact info for the primary contact. From that screen they can edit the data for the account and/or the contact. When it is saved, it is sent as one logical data set to a stored procedure which then saves the account info in one table, and the contact data goes to a different table, where the FKey matches the account PKey and primary is true.

As far as the user is concerned, it's one item, and it goes together. That's the philosophy I've tried to use when building SProcs, related items should go together.

Tg

Thanks for all your useful advise!!! i FINK i get what ur saying and it does make sense since the data is related!!! :ehh:

let me fink about it..il prop be bak asking some more info soon :eek2:

Thanks again :thumb: