Stored procedure problem
-
I am developing an application using SQL 2000 SP4. I created the following code for an invoicing procedure: When I try to run the code it does not do anything, however if I comment out all the lines before the insert statement it works fine. I just need to understand what is wrong with the code because this is my protection against double invoicing. When I run the code in Query Analyzer, it gives no errors but when I execute the procedure nothing happens.
Any help will be most appreciated
Create PROCEDURE udpInvoiceAviation
(@Folderid int,
@TxnID int = Null Output,
@RetMsg varchar(200) = Null Output,
@RetCode int = Null Output)
AS
SET NOCOUNT ON
Declare @InvoiceCheck int
Declare @InvoiceDetailCheck int
Select @RetCode = 1 , @RetMsg = ' '
Begin
If (Select Count(TxnID) from tblInvoiceDetails where FolderID = @FolderID and InvoiceLineClassRefListID = 1) = 0
If @InvoiceDetailCheck = 0
Select @RetCode = 0, @TxnID = 0, @RetMsg =IsNull(@RetMsg,'') + 'There are no Aviation Invoices, you can not create invoice without tickets' + Char(13) + Char(10)
End
Begin
If (Select Count(InvoiceNo) from tblInvoice where FolderID = @FolderID and InvoiceClass = 1) > 0
If @InvoiceCheck = 0
Select @RetCode = 0, @TxnID = 0, @RetMsg = IsNull(@RetMsg,'') + 'This service was already invoiced, you can not create another invoice' + Char(13) + Char(10)
End
If @RetCode = 0
Return
INSERT INTO dbo.tblInvoice
(FolderID, NoOfPax, Branch, CompanyCode, Customer, IssueDate, ReferenceNo, CCNumber, ExpiryDate, FolderOwner, TermsRefListID,
Currency, InvoiceType, TourismFileNo, Memo, InvoiceClass, InvoiceNo)
SELECT dbo.tblTravelFolder.FolderID, dbo.tblTravelFolder.NoOfPax, dbo.tblTravelFolder.Branch, dbo.tblTravelFolder.CompanyCode,
RTRIM(ISNULL(dbo.tblTravelFolder.ReqByLastName, '')) AS Customerreffullname, dbo.tblTravelFolder.ServiceDate As IssueDate,
dbo.tblTravelFolder.PONO AS PONO, dbo.tblTravelFolder.CCNumber AS CCNumber, dbo.tblTravelFolder.ExpiryDate AS ExpiryDate,
dbo.tblTravelFolder.FolderOwner, ISNULL(dbo.tblTravelFolder.Payment, N'Credit') AS IssueType, N'EGP' AS Currency,
dbo.tblTravelFolder.ServiceType AS InvoiceType, isnull(TourismFileNo,''), dbo.tblTravelFolder.AirlineMemo, N'1' AS InvoiceClass,
udfInvoiceNo.MaxInvoiceNo AS InvoiceNo
FROM dbo.tblTravelFolder CROSS JOIN
dbo.udfInvoiceNo() udfInvoiceNo
WHERE (dbo.tblTravelFolder.FolderID = @FolderID)
IF @@ROWCOUNT = 1
SELECT @TxnID = @@IDENTITY ,
@RetCode = 1,
@Retmsg = 'Txn number ' + Convert(varChar, @@IDENTITY) + ' added successfully'
ELSE
SELECT
@RetCode = 0,
@RetMsg = 'Insertion of new invoice failed, please check with the administrator'
RETURN
-
As far as I can tell the insert will always take place as @RetCode is never set to zero because you
declare @InvoiceCheck and @InvoiceDetailCheck but do not set their values and they will contain null.
What do you mean by the procedure does nothing, does it return any values in the parameters?
The proc will not show any output itself because you 'SET NOCOUNT ON'
Far away is close at hand in the images of elsewhere.
Anon. -
If I execute the procedure without commenting out all the lines above the insert line, nothing happens. I do check that by looking at the invoice tables which should show the inserts. If I remove all the code above the insert line, the tables will show the inserts.
-
I tested the logic of the code you posted and could not replicate your problem, the INSERT command was always executed, so I still stand by my original post.
I would have written the proc list this
Create PROCEDURE udpInvoiceAviation
@Folderid int,
@TxnID int = Null OUTPUT,
@RetMsg varchar(200) = Null OUTPUT,
@RetCode int = Null OUTPUT
AS
DECLARE @RowCT int
SET @RetCode = 1
SET @RetMsg = ' '
IF (SELECT COUNT(TxnID) FROM tblInvoiceDetails WHERE FolderID = @FolderID AND InvoiceLineClassRefListID = 1) = 0
BEGIN
SET @RetCode = 0
SET @TxnID = 0
SET @RetMsg = 'There are no Aviation Invoices, you can not create invoice without tickets' + Char(13) + Char(10)
RETURN
END
IF (SELECT COUNT(InvoiceNo) FROM tblInvoice WHERE FolderID = @FolderID and InvoiceClass = 1) > 0
BEGIN
SET @RetCode = 0
SET @TxnID = 0
SET @RetMsg = 'This service was already invoiced, you can not create another invoice' + Char(13) + Char(10)
RETURN
END
INSERT INTO dbo.tblInvoice
(FolderID, NoOfPax, Branch, CompanyCode, Customer, IssueDate, ReferenceNo, CCNumber, ExpiryDate, FolderOwner, TermsRefListID,
Currency, InvoiceType, TourismFileNo, Memo, InvoiceClass, InvoiceNo)
SELECT f.FolderID, f.NoOfPax, f.Branch, f.CompanyCode,
RTRIM(ISNULL(f.ReqByLastName, '')) AS [Customerreffullname], f.ServiceDate As IssueDate,
f.PONO AS [PONO], f.CCNumber AS [CCNumber], f.ExpiryDate AS [ExpiryDate],
f.FolderOwner, ISNULL(f.Payment, N'Credit') AS [IssueType], N'EGP' AS [Currency],
f.ServiceType AS [InvoiceType], ISNULL(TourismFileNo,''), f.AirlineMemo, N'1' AS [InvoiceClass],
i.MaxInvoiceNo AS [InvoiceNo]
FROM dbo.tblTravelFolder f
CROSS JOIN dbo.udfInvoiceNo() i
WHERE (f.FolderID = @FolderID)
SET @RowCT = @@ROWCOUNT
IF @RowCT = 1
BEGIN
SET @TxnID = @@IDENTITY
SET @RetCode = 1
SET @Retmsg = 'Txn number ' + Convert(varChar, @@IDENTITY) + ' added successfully'
END
ELSE
BEGIN
SET @RetCode = 0
SET @RetMsg = 'Insertion of new invoice failed, please check with the administrator'
END
RETURN
Far away is close at hand in the images of elsewhere.
Anon. -
This worked fine, I must have missed something in the typing. I really appreciate your effort as you can not imagine how much this helped me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply