However there are not any error message to occur, just passes it. Anyone can give me a tip?
I am a begineer of SQL.
I suspect it's this:
CREATE PROCEDURE sp_FinalizePurchase
@.Inv_shipAddress as varchar(50),
@.Inv_shipCity as VarChar(50),
@.Inv_shipProvince as VarChar(20),
@.Inv_shipPostalCode as Char(6),
@.Inv_shipPhone as VarChar(10),
@.INV_billAddress as VarChar(50),
@.INV_billCity as VarChar(50),
@.INV_billProvince as VarChar(20),
@.INV_billPostalCode as Char(6),
@.INV_billPhone as Char(10),
@.CUS_customerID as Int,
@.Inv_invoiceID as Int OutputAS
-- STEP 1
--Do the InsertINSERT tblInvoice
(Inv_date,
Inv_shipAddress,
Inv_shipCity,
Inv_shipProvince,
Inv_shipPostalCode,
Inv_shipPhone,
INV_billAddress,
INV_billCity,
INV_billProvince,
INV_billPostalCode,
INV_billPhone,
INV_status,
CUS_customerID
)
VALUES
(getdate(),
@.Inv_shipAddress,
@.Inv_shipCity,
@.Inv_shipProvince,
@.Inv_shipPostalCode,
@.Inv_shipPhone,
@.INV_billAddress,
@.INV_billCity,
@.INV_billProvince,
@.INV_billPostalCode,
@.INV_billPhone,
1,
@.CUS_customerid)--FIND OUT WHAT THEIR NEW ID IS
SELECT @.Inv_invoiceid = @.@.identity-- STEP 2:
--Do the Insert
INSERT Into tblInvoiceLine
(INV_invoiceID,
PRD_productID,
ILN_description,
ILN_quantity,
ILN_unitprice
)
SELECT DISTINCT
@.Inv_invoiceid,
tblBasket.productID,
tblMovie.mov_description,
tblBasket.quantity,
tblProduct.PRD_Price
FROM tblBasket, tblProduct, tblMovie
WHERE tblBasket.productID=tblProduct.PRD_ProductID
SELECT DISTINCT
@.Inv_invoiceid,
tblBasket.productID,
tblMovie.mov_description,
tblBasket.quantity,
tblProduct.PRD_Price
FROM tblBasket, tblProduct, tblMovie
WHERE tblBasket.productID=tblProduct.PRD_ProductID
You're not giving it any join criteria.|||Thank your hints, I have added another join criteria as follows but it still does not work correctly,just skips it.
SELECT DISTINCT
@.Inv_invoiceid,
tblBasket.productID,
tblMovie.mov_description,
tblBasket.quantity,
tblProduct.PRD_Price
FROM tblBasket, tblProduct, tblMovie
WHERE tblBasket.productID=tblProduct.PRD_ProductID
AND tblProduct.MOV_movieID=tblMovie.MOV_MovieID
Any suggestions I can get from your guys?|||remove the@. sign at the second line.
hth|||Does the Select query work in Query Analyzer? If the select query works, then add the insert to the beginning of it and see if that works. If that doesn't, it's either because the select query returns no records, there is a syntax error, or some constraint has been violated. QA will give you a hint in any of those cases. Try that.
Also, a more standard syntax for joins is:
|||First, I would like to say I have solved the problem. Thank your guys's hints.
SELECT DISTINCT
@.Inv_invoiceid,
tblBasket.productID,
tblMovie.mov_description,
tblBasket.quantity,
tblProduct.PRD_Price
FROM tblBasket b
join tblProduct p
on b.productID = p.PRD_ProductID
join tblMovie m
on p.MOV_movieID = m.MOV_MovieID
Second, I want to share my experience with your guys.
To upstairs 2 friends:
ndinakar says should "remove the @. sign at the second line.", I have to say this is wrong. Because the parameter @.Inv_invoiceid come from the the first table and it is a variable.
As I have told, the SP runs properly in QA, but it does not insert the data into the table. Paraigh analyse the reasons, "it's either because the select query returns no records, there is a syntax error, or some constraint has been violated." what you said is partly correct. This is really a "syntax error" but QA skips it. I look into it carefully and eventually I find the reason occurs in the tblMovie.mov_description field. In tblmovie, I claim the varchar length 255, but in tblinvoiceline I claim the varchar length is 50. It passes the QA but occurs error in runtime.
I think this is syntax error but QA seems no regard it as an error and skips it. In my opinion, this is a needed improvement aspects in SQL Server.
