HOWTO MSSql Stored Procedure

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
EnvisionIT
Posts: 2
Joined: Mon 13 Jul 2015 04:39

HOWTO MSSql Stored Procedure

Post by EnvisionIT » Mon 13 Jul 2015 04:57

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: HOWTO MSSql Stored Procedure

Post by azyk » Thu 16 Jul 2015 13:57

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.

Post Reply