1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| CREATE PROCEDURE [add_bank]
@name nvarchar (255)= NULL,
@code nvarchar (255)= NULL,
@bic nvarchar (255)= NULL,
@country nvarchar (255) = NULL,
@bank_account_number nvarchar (255) = NULL,
@iban nvarchar (255) = NULL,
@user_id int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
BEGIN TRANSACTION
SET NOCOUNT ON;
declare @bank_id int;
declare @bac_id int;
declare @country_id int;
declare @bank_exist int;
declare @account_exist int;
SELECT @bank_id = ISNULL(MAX([id]),0)+1 FROM BANK;
SELECT @country_id = id FROM COUNTRY WHERE name = @country;
SELECT @bac_id = ISNULL(MAX([id]),0)+1 FROM BANK_ACCOUNT;
SELECT @bank_exist = COUNT(*) FROM BANKS WHERE code = @code;
SELECT @account_exist = COUNT(*) FROM BANK_ACCOUNT WHERE iban = @iban;
IF (@bank_exist = 0) AND (@account_exist = 0)
BEGIN
INSERT INTO dbo.BANK
(id,
name,
code,
bic_number,
c_id)
VALUES (@bank_id
,@name
,@code
,@bic
,@country_id);
INSERT INTO dbo.BANK_ACCOUNT
(id,
account_number,
iban,
user_id,
b_id)
VALUES (@bac_id
,@bank_account_number
,@iban
,@user_id
,@bank_id);
END
ELSE
BEGIN
IF(@account_exist = 0) AND (@bank_exist > 0)
BEGIN
INSERT INTO dbo.BANK_ACCOUNT
(id,
account_number,
iban,
user_id,
b_id)
VALUES (@bac_id
,@bank_account_number
,@iban
,@user_id
,@bank_exist);
END
END
COMMIT TRANSACTION
END |
Partager