Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL StoredProcedures
I want to write a stored procedure for my invoice reportviewer.
After invoice is generated in reportviewer it must also add the data to my Invoice table
 
This is all my tables in my database
CREATE TABLE [dbo].[Waybills](
	[WaybillID] [int] IDENTITY(1,1) NOT NULL,
	[SenderName] [varchar](50) NULL,
	[SenderAddress] [varchar](50) NULL,
	[SenderContact] [int] NULL,
	[ReceiverName] [varchar](50) NULL,
	[ReceiverAddress] [varchar](50) NULL,
	[ReceiverContact] [int] NULL,
	[UnitDescription] [varchar](50) NULL,
	[UnitWeight] [int] NULL,
	[DateReceived] [date] NULL,
	[Payee] [varchar](50) NULL,
	[CustomerID] [int] NULL,
PRIMARY KEY CLUSTERED 
 
CREATE TABLE [dbo].[Customer](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[customerName] [varchar](30) NULL,
	[CustomerAddress] [varchar](30) NULL,
	[CustomerContact] [varchar](30) NULL,
	[VatNo] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
)
CREATE TABLE [dbo].[Cycle](
	[CycleID] [int] IDENTITY(1,1) NOT NULL,
	[CycleNumber] [int] NULL,
	[StartDate] [date] NULL,
	[EndDate] [date] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Payments](
	[PaymentID] [int] IDENTITY(1,1) NOT NULL,
	[Amount] [money] NULL,
	[PaymentDate] [date] NULL,
	[CustomerID] [int] NULL,
PRIMARY KEY CLUSTERED
 
Create table Invoices
(
InvoiceID int IDENTITY(1,1),
InvoiceNumber int,
InvoiceDate date,
BalanceBroughtForward money,
OutstandingAmount money,
CustomerID int,
WaybillID int,
PaymentID int,
CycleID int
PRIMARY KEY (InvoiceID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (WaybillID) REFERENCES Waybills(WaybillID),
FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
FOREIGN KEY (CycleID) REFERENCES Cycle(CycleID)
)
I want my sp to find all waybills for specific customer in a specific cycle with payments made from this client
 
All this data must then be added into INVOICE table
 
My form has a place where user can pick client and cycle and then a generate button to generate report and add that data to my invoice table
 
Can someone please help me or show me on the right direction
 
[MOVED FROM ANSWER]
create proc GenerateInvoice
@StartDate date,
@EndDate date,
@Payee varchar(30)
 
AS
 
SELECT Waybills.WaybillNumber Waybills.SenderName, Waybills.SenderAddress, Waybills.SenderContact, Waybills.ReceiverName, Waybills.ReceiverAddress,
Waybills.ReceiverContact, Waybills.UnitDescription, Waybills.UnitWeight, Waybills.DateReceived, Waybills.Payee,
Payments.Amount, Payments.PaymentDate, Cycle.CycleNumber, Cycle.StartDate, Cycle.EndDate
FROM Waybills CROSS JOIN
Payments CROSS JOIN
Cycle
WHERE Waybills.ReceiverName = @Payee
AND (Waybills.DateReceived BETWEEN (@StartDate) AND (@EndDate))
 
Insert Into Invoices (InvoiceNumber, InvoiceDate, BalanceBroughtForward, OutstandingAmount)
Values (@InvoiceNumber, @InvoiceDate, @BalanceBroughtForward, @ OutstandingAmount)
 
go

 
This is how far i get but it doesnt seem right
[/MOVED FROM ANSWER]
Posted 15-Sep-12 0:31am
Edited 16-Sep-12 5:52am
136.1K
v3
Comments
Vani Kulkarni at 15-Sep-12 6:56am
   
What have you tried till now? Are you stuck somewhere. Please post speciifc question instead posting your requirements here. I hope you have read instructions on "How to get the answer for your question" while posting this question. Try first then post the specific issue.
GoggatjieLiesl at 16-Sep-12 3:05am
   
create proc GenerateInvoice
@StartDate date,
@EndDate date,
@Payee varchar(30)

AS

SELECT Waybills.WaybillNumber Waybills.SenderName, Waybills.SenderAddress, Waybills.SenderContact, Waybills.ReceiverName, Waybills.ReceiverAddress,
Waybills.ReceiverContact, Waybills.UnitDescription, Waybills.UnitWeight, Waybills.DateReceived, Waybills.Payee,
Payments.Amount, Payments.PaymentDate, Cycle.CycleNumber, Cycle.StartDate, Cycle.EndDate
FROM Waybills CROSS JOIN
Payments CROSS JOIN
Cycle
WHERE Waybills.ReceiverName = @Payee
AND (Waybills.DateReceived BETWEEN (@StartDate) AND (@EndDate))

Insert Into Invoices (InvoiceNumber, InvoiceDate, BalanceBroughtForward, OutstandingAmount)
Values (@InvoiceNumber, @InvoiceDate, @BalanceBroughtForward, @ OutstandingAmount)

go

 
This is how far i get but it doesnt seem right
digimanus at 17-Sep-12 10:26am
   
Why do you use CROSS JOIN in stead of LEFT OUTER JOIN?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 370
1 Gihan Liyanage 338
2 ChauhanAjay 180
3 Vinay Mistry 160
4 Sergey Alexandrovich Kryukov 130
0 Sergey Alexandrovich Kryukov 9,011
1 OriginalGriff 7,941
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 16 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100