Вопрос # 4 692: …Подскажите пожалуйста как мне вызывать процедуру stored procedure-у MS… (Программирование баз данных (БД))

Здравствуйте, эксперты!
Подскажите пожалуйста как мне вызывать процедуру stored procedure-у MSSQL в Delphi?

Приложение:

USE [bonus_dbTest]
GO
/****** Object: StoredProcedure [dbo].[Commit_Actions] Script Date: 11/01/2010 11:44:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Commit_Actions]
AS
DECLARE
@err int,
@action_id int,
@member_id int,
@enabled tinyint,
@date_added datetime,
@birthday_date tinyint,
@equal_date datetime,
@limit money,
@date_from datetime,
@date_to datetime,
@bonus_money money,
@bonus_percent money,
@description varchar(100),
@comment varchar(100),
@card_id int,
@bonus money,
@date_of_birth datetime,
@void int,
@current_bonus money,
@doc_summs money;
SELECT action_id,
member_id,
enabled,
date_added,
birthday_date,
equal_date,
limit,
date_from,
date_to,
bonus_money,
bonus_percent,
description,
comment
FROM action
WHERE enabled = 1 and
getdate() between ISNULL(date_from, convert (datetime, '010101', 112)) and
ISNULL(date_to, convert (datetime, '490101', 112));
DECLARE c_cardowner CURSOR FOR
SELECT card_id,
bonus,
date_of_birth
FROM vcard
WHERE active = 1;
BEGIN
/* set nocount on;
set @err = 0;
begin transaction;
OPEN c_action;
FETCH NEXT FROM c_action INTO @action_id, @member_id, @enabled, @date_added, @birthday_date,
@equal_date, @limit, @date_from, @date_to, @bonus_money, @bonus_percent, @description,
@comment;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @equal_date IS NOT NULL and DATEDIFF(day, @equal_date, getdate()) = 0 begin
OPEN c_cardowner;
FETCH NEXT FROM c_cardowner INTO @card_id, @bonus, @date_of_birth;
WHILE @@FETCH_STATUS = 0
BEGIN
if @bonus_money IS NULL set @bonus_money = (@bonus * @bonus_percent)/100;
select @void = 1
from doc
where card_id = @card_id and
oper_type = 5 and
DATEDIFF(day, doc_date, getdate()) = 0 and
doc_items = @action_id and
id_1C = '';
IF @@ROWCOUNT = 0 and @bonus_money > 0 begin
select @current_bonus = bonus + @bonus_money
from vcurrent_card
where card_id = @card_id;
update card
set bonus = @current_bonus
where card_id = @card_id and
active = 1;
insert into doc(id_1C, card_id, sync_id, oper_type, doc_number, doc_date,
doc_type, doc_summ, doc_bonus, doc_items)
values ('', @card_id, 0, 5, '', getdate(), @description + @comment, 0, @bonus_money,
@action_id);
end;
FETCH NEXT FROM c_cardowner INTO @card_id, @bonus, @date_of_birth;
END;
CLOSE c_cardowner;
END;
IF @birthday_date = 1 begin
OPEN c_cardowner;
FETCH NEXT FROM c_cardowner INTO @card_id, @bonus, @date_of_birth;
WHILE @@FETCH_STATUS = 0
BEGIN
if @bonus_money IS NULL set @bonus_money = (@bonus * @bonus_percent)/100;
select @void = 1
from doc
where card_id = @card_id and
oper_type = 5 and
DATEDIFF(day, doc_date, getdate()) = 0 and
doc_items = @action_id and
id_1C = '';
IF @@ROWCOUNT = 0 and @bonus_money > 0 and DATEPART(month,getdate()) =
DATEPART(month,@date_of_birth) and DATEPART(day,getdate()) = DATEPART(day,
@date_of_birth) begin
select @current_bonus = bonus + @bonus_money
from vcurrent_card
where card_id = @card_id;
update card
set bonus = @current_bonus
where card_id = @card_id and
active = 1;
insert into doc(id_1C, card_id, sync_id, oper_type, doc_number, doc_date,
doc_type, doc_summ, doc_bonus, doc_items)
values ('', @card_id, 0, 5, '', getdate(), @description + @comment, 0, @bonus_money,
@action_id);
end;
FETCH NEXT FROM c_cardowner INTO @card_id, @bonus, @date_of_birth;
END;
CLOSE c_cardowner;
END;
IF @limit IS NOT NULL begin
OPEN c_cardowner;
FETCH NEXT FROM c_cardowner INTO @card_id, @bonus, @date_of_birth;
WHILE @@FETCH_STATUS = 0
BEGIN
if @bonus_money IS NULL set @bonus_money = (@bonus * @bonus_percent)/100;
select @doc_summs = ISNULL(sum(doc_summ),0)
from doc
where card_id = @card_id and
oper_type in (1, 2) and
doc_date between ISNULL(@date_from, convert (datetime, '010101', 112)) and
ISNULL(@date_to, convert (datetime, '490101', 112));
select @void = 1
from doc
where card_id = @card_id and
oper_type = 5 and
doc_items = @action_id and
id_1C = '';
IF @@ROWCOUNT = 0 and @bonus_money > 0 and @doc_summs > @limit begin
select @current_bonus = bonus + @bonus_money
from vcurrent_card
where card_id = @card_id;
update card
set bonus = @current_bonus
where card_id = @card_id and
active = 1;
insert into doc(id_1C, card_id, sync_id, oper_type, doc_number, doc_date,
doc_type, doc_summ, doc_bonus, doc_items)
values ('', @card_id, 0, 5, '', getdate(), @description + @comment, 0, @bonus_money,
@action_id);
end;
FETCH NEXT FROM c_cardowner INTO @card_id, @bonus, @date_of_birth;
END;
CLOSE c_cardowner;
END;
FETCH NEXT FROM c_action INTO @action_id, @member_id, @enabled, @date_added,
@birthday_date,
@equal_date, @limit, @date_from, @date_to, @bonus_money, @bonus_percent, @description,
@comment;
END;
CLOSE c_action;
Commit Transaction;
DEALLOCATE c_action;
DEALLOCATE c_cardowner;
if @@Error <> 0 or @err <> 0 goto PROBLEM;
return @err;
PROBLEM:
return 1;
*/
return 1;
END

Мини-форум вопроса

Чтобы оставлять сообщения в мини-форумах, Вы должны авторизироваться на сайте.

Экспертная система Delphi.int.ru

Сообщество программистов
Общение, помощь, обмен опытом

Delphi.int.ru Expert

Другие разделы портала

Переход к вопросу:

Статистика за сегодня:

Лучшие эксперты

Вопрос # 4 692

Мини-форум вопроса

Экспертная система Delphi.int.ru

Сообщество программистовОбщение, помощь, обмен опытом

Delphi.int.ru Expert

Другие разделы портала

Переход к вопросу:

Статистика за сегодня:

Лучшие эксперты

Вопрос # 4 692

Мини-форум вопроса

Сообщество программистов
Общение, помощь, обмен опытом