> Keeping in mind that I may be calling the same sp for insert of a new
> row of. updating an exsisting role, what's the best approach/design for
> this sp.

"Jason" <jason@cyberpine.com> wrote in message
news:1160867354.102018.6160@b28g2000cwb.googlegroups.com...
> In a common security tables design with
>
> users
> roles
> userroles
>
>
> table users:
> userid (k)
> name
> email
>
> table roles:
> roleid (k)
> role
>
> table userroles:
> roleid (fk)
> userid (fk)
>
> and a view combining both functions
>
> userview :
> name email role userid roleid
>
> I'm looking to maintain security via a single asp.net gridview that
> will have a single update stored procedure for both edit and/or insert.
> The gridview will displays the view as follows:
>
> name email role
>
> where role is a dropdownlist showing all possible roles in update and
> insert.
>
> Keeping in mind that I may be calling the same sp for insert of a new
> row of. updating an exsisting role, what's the best approach/design for
> this sp.
>
> I have a very crude working sample, that I'm sure is not very smart. It
> does of a delete from user and
> userroles, before inserting rows with new values back in every time.
>
> Is there a better approach? beyond using the built in asp.net 2.0
> membership which I am not looking to move to for this application.
>

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:efteg0B8GHA.2384@TK2MSFTNGP04.phx.gbl...
> Jason
>> Keeping in mind that I may be calling the same sp for insert of a new
>> row of. updating an exsisting role, what's the best approach/design for
>> this sp.
>
> I'd prefer having separate stored procedure for each DML
> (insert/update/delete)
>. . .

> IMO, stored procedures that do nothing but wrap single DML statements are
> a waste of time and a missed opportunity.

>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:efteg0B8GHA.2384@TK2MSFTNGP04.phx.gbl...
>> Jason
>>> Keeping in mind that I may be calling the same sp for insert of a new
>>> row of. updating an exsisting role, what's the best approach/design for
>>> this sp.
>>
>> I'd prefer having separate stored procedure for each DML
>> (insert/update/delete)
>>. . .
>
> IMO, stored procedures that do nothing but wrap single DML statements are
> a waste of time and a missed opportunity.
>
> It's not insert/update/delete that should get procedures, it's the atomic
> logical operations on your schema. If your schema is well-designed an
> atomic logical operation will usually map onto a single DML statement, but
> it's an important conceptual difference. But even where a single
> operation on a single table is involved, the stored procedures should
> provide a level of abstraction from the base schema. For instance here,
> you might well want to hide the surrogate key structure of your tables
> from the client, on the theory that the right amount of information to
> require the client to provide to add a user to a role is the natural key
> of the role and the natural key of the user, and having the client provide
> the surrogate keys exposes too much implementation detail.
>
> Eg
>
> create procedure AddUserToRole @UserName varchar(50), @RoleName
> varchar(50)
> as
> begin
>
> insert into UserRole(UserID,RoleID)
> select (select UserID from Users where UserName = @UserName),
> (select RoleId from Roles where RoleName = @RoleName)
>
> end
>
>
> instead of
> create procedure UserRole_insert @UserID int, @RoleID int
> as
> begin
> insert into UserRole (UserID,RoleID)
> values (@UserID, @RoleID)
> end
>
>
> David

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:OUwZBWO8GHA.4620@TK2MSFTNGP02.phx.gbl...
>> IMO, stored procedures that do nothing but wrap single DML statements are
>> a waste of time and a missed opportunity.
>
> I disagree with you. It depends on the logic you have behind as well as
> business requirements.
> What if you are going to update/insert/delete large amount of data on
> provided parameters.

>why would you think that ita wastes of time?

>
> Also consider inserting a max value into the table that based on select
> max(col) from table with (updlock,holdlock)

>
> Actually ,David I don't understand your point of wasting of time and a
> missing opportunity because it depends on many parameters as a I said
> before and I think it should be designed as per requierement
>
> And in your example why not to check if the user does exist in the system
> and then perform inserting.
>

> Stored procedures aren't significantly faster than prepared statements.

> Because you should be writing stored procedures that make the client's
> programming job simpler or more efficient.

> Absolutely, that would be a good idea. And that captures my point
> exactly. An operation like AddUserToGroup can be idempotent. It makes
> sense that if a user is already in a group, then you would return without
> raising an error. However if you are just wrapping an INSERT, you need to
> throw an error. UserGroup_Insert _must_ fail if there is a duplicate row
> in UserGroup.

>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:OUwZBWO8GHA.4620@TK2MSFTNGP02.phx.gbl...
>>> IMO, stored procedures that do nothing but wrap single DML statements
>>> are a waste of time and a missed opportunity.
>>
>> I disagree with you. It depends on the logic you have behind as well as
>> business requirements.
>> What if you are going to update/insert/delete large amount of data on
>> provided parameters.
>
> Stored procedures aren't significantly faster than prepared statements.
>
>>why would you think that ita wastes of time?
>
> Because you should be writing stored procedures that make the client's
> programming job simpler or more efficient.
>
>>
>> Also consider inserting a max value into the table that based on select
>> max(col) from table with (updlock,holdlock)
>
> Yes. It's just that kind of extra logic that stored procedures should
> encapsulate.
>
> I wasn't suggesting that you should never wrap single DML in a procedure,
> just you shouldn't do it routinely. Stored procedures should add value by
> encapsulating the details of your schema design and provide a "service
> interface" for your database.
>
>>
>> Actually ,David I don't understand your point of wasting of time and a
>> missing opportunity because it depends on many parameters as a I said
>> before and I think it should be designed as per requierement
>>
>> And in your example why not to check if the user does exist in the system
>> and then perform inserting.
>>
>
> Absolutely, that would be a good idea. And that captures my point
> exactly. An operation like AddUserToGroup can be idempotent. It makes
> sense that if a user is already in a group, then you would return without
> raising an error. However if you are just wrapping an INSERT, you need to
> throw an error. UserGroup_Insert _must_ fail if there is a duplicate row
> in UserGroup.
>
> David

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:e3lgZua8GHA.4996@TK2MSFTNGP03.phx.gbl...
>> Stored procedures aren't significantly faster than prepared statements.
>
> http://www.sql-server-performance.com/stored_procedures.asp
> . .