Stored proc issue... Best parameter type to use?

From: Alexis M (nospam_at_hotmail.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 14:11:27 GMT

Greetings,

I currently have a stored procedure that performs a user-selected operation
on a row, something like:

CREATE PROC sp_AlterRow
(
    RowID INTEGER,
    Operation INTEGER -- e.g. a number from 1 to 10 for the 10 valid
operations
    Errors VARCHAR(255) OUTPUT
)
AS....

This returns 0 if successful, 1 otherwise. The stored proc is transactional,
i.e. a ROLLBACK is executed if any of the processing fails.

I now need to create a "parent" stored procedure to call this one and
perform the same operation on multiple rows. This will be an "all or none"
operation, i.e. if any of the calls fail, the entire process will be rolled
back. i.e.

CREATE PROC sp_AlterAllOrNone
(
    <some parameter(s) indicating which rows to process>,
    Operation INTEGER,
    Errors VARCHAR(255) OUTPUT
)
AS
set @total = 0
BEGIN TRAN
For each <row> in <rows to process>
    EXEC @result=sp_AlterRow <row>, Operation....
    set @total=total+@result
next

IF @total<>0 ROLLBACK TRAN

Or something like that... my question is, how is it best to define which
rows to perform the operation on? I considered using a VARCHAR value to
store the RowIDs as delimited text, and split these into separate values.
however this can introduce errors and unpredictable behaviour, as this
"parent" procedure will be called by a number of clients (some developed in
.NET, other stored procedures, etc).

Another way I thought of was to have a table which the client would insert
rows into (two columns: <RowID>, <BatchNo>) and the parent stored proc will
be called with the "batch no" of which rows to process. However this adds
extra complexity to the client-side development.

I'd be extremely greatful for any other ideas you ladies and gentlemen may
have for me!

Thanks,
Alexis

< a l e x _ m 7 4 at h o t m a i l dot c o m > 

--

Relevant Pages

  • Persistent Schema-Stability (Sch-S) Locks
    ... locks on this table that linger and stay locked for an extended period ... Locks/Objects view always shows one stored procedure in particular. ... As soon as I kill the process with the Sch-S lock then the CPU usage ... If the "Last TSQL Command Batch" shows this stored proc name all the ...
    (microsoft.public.sqlserver.programming)
  • RE: EXEC Stored Procedure does not work in SSIS task
    ... Your original version had a proc that, before it could do it thing, had to ... Result Set page to map the return to Variables in the SSIS Package. ... Set up your Source adapter and put in your command as: ... The stored procedure sp_GetTrafficData executes another stored procedure the ...
    (microsoft.public.sqlserver.dts)
  • RE: parameter name problem
    ... stored procedure positionally. ... if you have a proc with two parameters you can call it as: ... > sql server and in oracle it is a simple string, ...
    (microsoft.public.sqlserver.programming)
  • Re: Why use stored procedure in C#?
    ... But I am not clear about why using the stored procedure. ... I can do that perfectly fine without a proc. ... Lead developer of LLBLGen Pro, the productive O/R mapper for .NET ... My .NET blog: http://weblogs.asp.net/fbouma ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: OraOLEDB.Oracle : How to get an updatable ADODB Recordset from
    ... if not i think the microsoft API adds the rowid in the background to ... Reading data from a stored procedure in an ADODB Recordset ... Modify the disconnected Recordset on the Client ...
    (microsoft.public.data.ado)