stored procedure & choices
-
hi;
i want to enter data in invoice payment table.. payment type is cheque; cash and credit card depending on the user. i've writter followin procedure but if i use cheque; my application says u must enter data for credit card. but i don't want to do that. i just want to enter the data depending on my needs that's whether payment is cheque, cash or credit card
CREATE PROCEDURE dbo.SP_InsertNewInvoice
@mobileNo bigInt,
@invoicePaymentId int,
@customerName nvarchar(50),
@invoiceAmount decimal,
@invoiceBalance decimal,
@invoicePaymentDate DateTime,
@payCode nvarchar(50),
@referenceNo nvarchar(50),
@locOut nvarchar(50),
@bankName nvarchar(50),
@branchName nvarchar(50),
@cardName nvarchar(50),
@expirationDate DateTime,
@authorizationCode nvarchar(50)
as
Begin
Declare @customerId nvarchar(50)
Select @customerId = customerId From Customer where customerName = @customerName AND customer.mobileNo = @mobileNo
INSERT INTO InvoicePayment(invoicePaymentId, invoiceAmount, invoiceBalance, invoicePaymentDate)
VALUES (@invoicePaymentId, @invoiceAmount, @invoiceBalance, @invoicePaymentDate)
INSERT INTO Payment(mobileNo, customerId, invoicePaymentId, payCode, referenceNo)
VALUES (@mobileNo, @customerId, @invoicePaymentId, @payCode, @referenceNo)
INSERT INTO paymentTypes(paymentId, payCode)
VALUES (@invoicePaymentId, @payCode)
If @payCode = 'Cheque'
INSERT INTO ChequePayment(paymentId, locOut, bankName, branchName)
VALUES(@invoicePaymentId, @locOut, @bankName, @branchName)
Else If @payCode = 'CreditCard'
INSERT INTO CreditCardPayment(paymentId, authorizationCode, expirationDate, cardName)
VALUES(@invoicePaymentId, @authorizationCode, @expirationDate, @cardName)
Else
INSERT INTO CashPayment(paymentId, cashReceiptNo)
VALUES(@invoicePaymentId, @referenceNo)
End
GO
-
Tell your application to stop doing that.
the stored procedure looks fine.
-
i wishhhhhhhhh i can do that
but problem is when i test in query analyzer; it also says these values must bed entered
-
Enter the lowly NULL value.
The credit card info columns in your target table(s) must allow null values...
CREATE TABLE dbo.PaymentInfo(
....(other columns)...
@cardName nvarchar(50) NULL,
@expirationDate DateTime NULL,
@authorizationCode nvarchar(50) NULL, -- nvarchar? do you get Chinese auth codes....?
...
...that will allow you to insert data into the table without CC data
To call your procedure without passing values for specific parameters, set the default values to NULL for those parameters:
CREATE PROC dbo.InvoiceInsert
@mobileNo bigInt,
@invoicePaymentId int,
@customerName nvarchar(50),
@invoiceAmount decimal,
@invoiceBalance decimal,
@invoicePaymentDate DateTime,
@payCode nvarchar(50),
@referenceNo nvarchar(50),
@locOut nvarchar(50),
@bankName nvarchar(50),
@branchName nvarchar(50),
@cardName nvarchar(50) = NULL,
@expirationDate DateTime = NULL,
@authorizationCode nvarchar(50) = NULL
... and then you can call the procedure without specifying @CardName, @ExprirationDate, or @AuthorizationCode
Note: you should reserve the 'sp_' prefix for names of stored procedures you will place in the master database, and plan on calling from all other databases. Placing 'sp_' at the beginning of a stored procedure name directs the query processor to search for your procedure in the master database first.
-Eddie
Eddie Wuerch
MCM: SQL -
yes; i got it. thanx
-
Hi,
CREATE TABLE [Payment] (
[mobileNo] [bigint] NOT NULL ,
[customerId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[referenceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[payCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[invoicePaymentId] [int] NULL ,
[securityPaymentId] [int] NULL ,
[billPaymentId] [int] NULL ,
CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED
(
[mobileNo]
) ON [PRIMARY] ,
CONSTRAINT [FK_Payment_BillPayment] FOREIGN KEY
(
[billPaymentId]
) REFERENCES [BillPayment] (
[billpaymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Payment_InvoicePayment] FOREIGN KEY
(
[invoicePaymentId]
) REFERENCES [InvoicePayment] (
[invoicePaymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Payment_paymentTypes] FOREIGN KEY
(
[payCode]
) REFERENCES [paymentTypes] (
[payCode]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Payment_SecurityPayment] FOREIGN KEY
(
[securityPaymentId]
) REFERENCES [SecurityPayment] (
[securityPaymentId]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [paymentTypes] (
[payCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[paymentId] [int] NULL ,
CONSTRAINT [PK_paymentTypes] PRIMARY KEY CLUSTERED
(
[payCode]
) ON [PRIMARY] ,
CONSTRAINT [FK_paymentTypes_CashPayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [CashPayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_paymentTypes_ChequePayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [ChequePayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_paymentTypes_CreditCardPayment] FOREIGN KEY
(
[paymentId]
) REFERENCES [CreditCardPayment] (
[paymentId]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [CashPayment] (
[paymentId] [int] NOT NULL ,
[cashPaymentDate] [datetime] NULL ,
[cashReceiptNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CashPayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ChequePayment] (
[paymentId] [int] NOT NULL ,
[locOut] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bankName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[branchName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ChequePayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CreditCardPayment] (
[paymentId] [int] NOT NULL ,
[expirationDate] [datetime] NULL ,
[cardName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[authorizationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_CreditCardPayment] PRIMARY KEY CLUSTERED
(
[paymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [BillPayment] (
[billpaymentId] [int] NOT NULL ,
[billPaymentDate] [datetime] NULL ,
[newBillBalance] [decimal](18, 0) NULL ,
[billAmount] [decimal](18, 0) NULL ,
CONSTRAINT [PK_BillPayment] PRIMARY KEY CLUSTERED
(
[billpaymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [InvoicePayment] (
[invoicePaymentId] [int] NOT NULL ,
[invoiceBalance] [decimal](18, 0) NULL ,
[invoicePaymentDate] [datetime] NULL ,
[invoiceAmount] [decimal](18, 0) NULL ,
CONSTRAINT [PK_InvoicePayment] PRIMARY KEY CLUSTERED
(
[invoicePaymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SecurityPayment] (
[securityPaymentId] [int] NOT NULL ,
[currentDeposit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additionalDeposit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amount] [decimal](19, 0) NULL ,
[securityBalance] [decimal](18, 0) NULL ,
[securityDepositDate] [datetime] NULL ,
CONSTRAINT [PK_SecurityPayment] PRIMARY KEY CLUSTERED
(
[securityPaymentId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here it is Invoice Store Procedure:
CREATE PROCEDURE dbo.SP_InsertNewInvoice
@mobileNo bigInt,
@invoicePaymentId int,
@customerName nvarchar(50),
@invoiceAmount decimal,
@invoiceBalance decimal,
@invoicePaymentDate DateTime,
@payCode nvarchar(50),
@referenceNo nvarchar(50),
@locOut nvarchar(50),
@bankName nvarchar(50),
@branchName nvarchar(50),
@cardName nvarchar(50),
@expirationDate DateTime,
@authorizationCode nvarchar(50)
as
Begin
Declare @customerId nvarchar(50)
Select @customerId = customerId From Customer where customerName = @customerName AND customer.mobileNo = @mobileNo
INSERT INTO InvoicePayment(invoicePaymentId, invoiceAmount, invoiceBalance, invoicePaymentDate)
VALUES (@invoicePaymentId, @invoiceAmount, @invoiceBalance, @invoicePaymentDate)
INSERT INTO Payment(mobileNo, customerId, invoicePaymentId, payCode, referenceNo)
VALUES (@mobileNo, @customerId, @invoicePaymentId, @payCode, @referenceNo)
INSERT INTO paymentTypes(paymentId, payCode)
VALUES (@invoicePaymentId, @payCode)
If @payCode = 'Cheque'
INSERT INTO ChequePayment(paymentId, locOut, bankName, branchName)
VALUES(@invoicePaymentId, @locOut, @bankName, @branchName)
Else If @payCode = 'CreditCard'
INSERT INTO CreditCardPayment(paymentId, authorizationCode, expirationDate, cardName)
VALUES(@invoicePaymentId, @authorizationCode, @expirationDate, @cardName)
Else
INSERT INTO CashPayment(paymentId, cashReceiptNo)
VALUES(@invoicePaymentId, @referenceNo)
End
GO
-
First, is PayCode (in paymentTypes) a value that is unique per payment, or is it a way of determining what type if payment (cheque/cash/credit card) was applied? If it is a reference code (one code for cheque, one for cash...), then paymentID should be removed from that table. If it is unique per payment, then drop the table and move the value to the payment tables.
How I normally approach these type of situations:
* Create payment-receipt tables for payments, creating separate tables for payment methods with different types of info to track (like you have done - cheque/cash/credit card).
* Any data specific to a payment, such as customer number, payment date, reconciliation date, etc., go into the payment tables.
* Relate payments to customers or accounts.
* Depending on the system, I may not relate payments to invoices.
* The key is that invoices and payments must be many-to-many, to make it possible for customers to make multiple payments on an invoice, and can use multiple payment types (for the customer who pays partially in cash, and finishes with a credt card). Often, it must be possible to accept a payment that spans multiple customers or accounts.
In many systems, payments should be loosely-coupled with invoices, balances, and accounts - meaning I may not tie these items together with foriegn keys. You never want to refuse a payment simply because your system was built too rigid to accept it. When a payment occurs, you capture as much data as possible about it. Then it can be reconciled against the account.
-Eddie
Eddie Wuerch
MCM: SQL -
1. yes; payCode is for cheque; cash or card payment. how can we make relation with paymentTypes if we remove paymentId from the table?????
2. if i loosely coupled invoices with payment then what should be the form of relation with it?? here is diagram
-
any idea??
-
no idea?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply