HOWTO MSSql Stored Procedure

[EnvisionIT]

Looking for the best / most efficient method to execute a stored procedure, this particular procedure runs very, very often, several times a sec updating client balances. Currently I just perform the sql statements in a TMSQuery, but run into timeout issues on the server. So was thinking I'd just move the code to a procedure.

Basically it is setup as:

CREATE PROCEDURE DoSomething @ClientId Int, @Balance decimal(10,2), @Result bit OUT
AS BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE ClientFlags SET LastUpdate = getdate() WHERE ClientID = @ClientId;
UPDATE ClientBal SET Balance = @Balance WHERE ClientID = @ClientId;
COMMIT TRANSACTION
SET @Result = 1;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @Result = 0;
END CATCH
END;

In SQL Explorer with a query to execute this I do the following:

@DECLARE @Result Int;
EXEC DoSomething @ClientId = 1, @Result = @Result OUTPUT;
SELECT @Result AS ResultSuccess;

So my questions:
1. Should I just drop a TMSQuery with the above 3 lines and then "Open" the query and check to see if MSQuery.FieldByName('ResultSucess').AsInteger = 1? Is this quick and efficient?

2. Should I be using a TMSStoredProc? There really are no instruction on how to do this.

3. Some other method?

[azyk]

Try using the TMSStoredProc component for transferring IN parameters to a stored procedure, its further calling and retrieving the OUT parameter value. The code for calling the DoSomething stored procedure can be like the following in run time:

Code: Select all

MSStoredProc.StoredProcName := 'DoSomething';
MSStoredProc.ParamByName('ClientId').AsInteger := 1;
MSStoredProc.ParamByName('Balance').AsCurrency := 1234.56;
MSStoredProc.Execute;
if MSStoredProc.ParamByName('Result').AsBoolean = True then
....

See more details about theTMSStoredProc component in our documentation: https://www.devart.com/sdac/docs/?stored_proc.htm .

See the sample of using TMSStoredProc in the SDACDemo project. The SDACDemo project is in the "Demos\SDACDemo\" folder relatively to the SDAC demos installation path.

If any questions remain or new ones come up, please contact us - we will answer them.