stored procedure

[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.

[pame1la]

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??????

[szlamany]

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:

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.

[techgnome]

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

[pame1la]

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

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

Thanks again