If you choose to implement business rules in stored procedures, the obvious
advantage of the shared proc approach is that you don't have to replicate
code. It's usually best to keep validation procs simple and pass validation
codes/messages via output parameters. Keep transaction handling in the
calling proc or client.
This approach works well for single-row insert/updates but a downside is
that you can't easily perform set-based processing (e.g. insert from staging
table with validation/calculation).
--
Hope this helps.
Dan Guzman
SQL Server MVP
[quoted text, click to view] "Tadwick" <Tadwick@discussions.microsoft.com> wrote in message
news:24580CEB-A824-4440-B050-5B780C70B00B@microsoft.com...
> Is it best to keep all insert and update statements in separate sprocs
> even
> if they share common business logic for validation/calculation etc? Is it
> possible and/or even preferable to create a single sproc for the business
> logic and call it from the separate insert and update sprocs? Does this
> affect how error messages are raised and transactions are handled?
>
> Tx, Tad
Thanks, Dan - I should have mentioned that this is for single row ops.
Thanks again for the advice.
Tad
[quoted text, click to view] "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:16B2B0BE-7329-493B-BABE-3A934AA67AAD@microsoft.com...
> If you choose to implement business rules in stored procedures, the
> obvious advantage of the shared proc approach is that you don't have to
> replicate code. It's usually best to keep validation procs simple and
> pass validation codes/messages via output parameters. Keep transaction
> handling in the calling proc or client.
>
> This approach works well for single-row insert/updates but a downside is
> that you can't easily perform set-based processing (e.g. insert from
> staging table with validation/calculation).
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Tadwick" <Tadwick@discussions.microsoft.com> wrote in message
> news:24580CEB-A824-4440-B050-5B780C70B00B@microsoft.com...
>> Is it best to keep all insert and update statements in separate sprocs
>> even
>> if they share common business logic for validation/calculation etc? Is
>> it
>> possible and/or even preferable to create a single sproc for the business
>> logic and call it from the separate insert and update sprocs? Does this
>> affect how error messages are raised and transactions are handled?
>>
>> Tx, Tad
>
