I have created table like this...
CREATE TABLE INVOICEMASTER(I NVOICEID INT IDENTITY(1,1),E XPORTER_REF INT,CONSIGNEE_R EF INT,BUYER_REF INT,AGENT_REF INT,INVOICENO VARCHAR(20),INV OICEDATE DATETIME,EXPORT ERSBILLREF VARCHAR(20),BUY ERORDERNO VARCHAR(200),BU YERORDERDATE DATETIME,OTHER_ REFERENCES VARCHAR(30),COU NTRYOFORIGIN VARCHAR(50),COU NTRYOFDESTINATI ON VARCHAR(50),PRE CARRIAGEBY NVARCHAR(100),P LACEOFRECEIPT NVARCHAR(100),V ESSEL_FLIGHTNO NVARCHAR(100),P ORTOFLOADING NVARCHAR(100),P ORTOFDISCHARGE NVARCHAR(100),F INALDESTINATION NVARCHAR(100),T ERMSOFDELVIERYN PAYMENT NVARCHAR(400),D ISCOUNTLABEL VARCHAR(100),DI SCOUNTRATE DECIMAL(4,2),DI SCOUNTAMOUNT DECIMAL(10,3),I NVOICETOTAL DECIMAL(14,3),A MOUNTINWORDS NVARCHAR(200),L CNO NVARCHAR(200),R EMARKS NVARCHAR(400),I NVOICELABEL VARCHAR(100),PR IMARY KEY(INVOICEID))
CREATE TABLE INVOICECHILD1 (INVOICEREF INT REFERENCES INVOICEMASTER (INVOICEID), MARKSANDCONTAIN ERSNO NVARCHAR (200), NUMBEROFPKGS VARCHAR (100), DESCRIPTIONOFGO ODS NVARCHAR (400),THICKNESS VARCHAR(100),CO LOUR VARCHAR(50),SEL ECTION VARCHAR(30),SIZ E NVARCHAR(300),U OM VARCHAR(10),CUR RENCYDESC VARCHAR(30))
and the below is store procedure query..
CREATE PROCEDURE INSERT_INVOICE( @EXPORTER VARCHAR(150),@C ONSIGNEE VARCHAR(150),@B UYER VARCHAR(150),@A GENT VARCHAR(150),@I NVOICENO VARCHAR(20),@IN VOICEDATE DATETIME,@EXPOR TERSBILLREF VARCHAR(20),@BU YERORDERNO VARCHAR(200),@B UYERORDERDATE DATETIME,@OTHER _REFERENCES VARCHAR(30),@CO UNTRYOFORIGIN VARCHAR(50),@CO UNTRYOFDESTINAT ION VARCHAR(50),@PR ECARRIAGEBY NVARCHAR(100),@ PLACEOFRECEIPT NVARCHAR(100),@ VESSEL_FLIGHTNO NVARCHAR(100),@ PORTOFLOADING NVARCHAR(100),@ PORTOFDISCHARGE NVARCHAR(100),@ FINALDESTINATIO N NVARCHAR(100),@ TERMSOFDELVIERY NPAYMENT NVARCHAR(400),@ MARKSANDCONTAIN ERSNO NVARCHAR (200), @NUMBEROFPKGS VARCHAR (100), @DESCRIPTIONOFG OODS NVARCHAR (400),@THICKNES S VARCHAR(100),@C OLOUR VARCHAR(50),@SE LECTION VARCHAR(30),@SI ZE NVARCHAR(300),@ UOM VARCHAR(10),@CU RRENCYDESC VARCHAR(30),@DI SCOUNTLABEL VARCHAR(100),@D ISCOUNTRATE DECIMAL(4,2),@D ISCOUNTAMOUNT DECIMAL(10,3),@ INVOICETOTAL DECIMAL(14,3),@ AMOUNTINWORDS NVARCHAR(200),@ LCNO NVARCHAR(200),@ REMARKS NVARCHAR(400),@ INVOICELABEL VARCHAR(100))
AS
BEGIN TRANSACTION
DECLARE @EXPORTER_REF AS INT
SET @EXPORTER_REF = 0
SELECT @EXPORTER_REF = EXPORTERID FROM EXPORTER WHERE EXPORTERNAME=@E XPORTER
DECLARE @CONSIGNEE_REF AS INT
SET @CONSIGNEE_REF = 0
SELECT @CONSIGNEE_REF = CONSIGNEEID FROM CONSIGNEE WHERE CONSIGNEENAME=@ CONSIGNEE
DECLARE @BUYER_REF AS INT
SET @BUYER_REF = 0
SELECT @BUYER_REF = BUYERID FROM BUYER WHERE BUYERNAME=@BUYE R
DECLARE @AGENT_REF AS INT
SET @AGENT_REF = 0
SELECT @AGENT_REF = AGENTID FROM AGENT WHERE AGENTNAME=@AGEN T
INSERT INTO INVOICEMASTER(E XPORTER_REF,CON SIGNEE_REF,BUYE R_REF,AGENT_REF ,INVOICENO,INVO ICEDATE,EXPORTE RSBILLREF,BUYER ORDERNO,BUYEROR DERDATE,OTHER_R EFERENCES,COUNT RYOFORIGIN,COUN TRYOFDESTINATIO N,PRECARRIAGEBY ,PLACEOFRECEIPT ,VESSEL_FLIGHTN O,PORTOFLOADING ,PORTOFDISCHARG E,FINALDESTINAT ION,TERMSOFDELV IERYNPAYMENT,DI SCOUNTLABEL,DIS COUNTRATE,DISCO UNTAMOUNT,INVOI CETOTAL,AMOUNTI NWORDS,LCNO,REM ARKS,INVOICELAB EL) VALUES (@EXPORTER_REF, @CONSIGNEE_REF, @BUYER_REF,@AGE NT_REF,@INVOICE NO,@INVOICEDATE ,@EXPORTERSBILL REF,@BUYERORDER NO,@BUYERORDERD ATE,@OTHER_REFE RENCES,@COUNTRY OFORIGIN,@COUNT RYOFDESTINATION ,@PRECARRIAGEBY ,@PLACEOFRECEIP T,@VESSEL_FLIGH TNO,@PORTOFLOAD ING,@PORTOFDISC HARGE,@FINALDES TINATION,@TERMS OFDELVIERYNPAYM ENT,@DISCOUNTLA BEL,@DISCOUNTRA TE,@DISCOUNTAMO UNT,@INVOICETOT AL,@AMOUNTINWOR DS,@LCNO,@REMAR KS,@INVOICELABE L)
DECLARE @INVOICEID CHAR(6)
SELECT @INVOICEID = MAX(INVOICEID) FROM INVOICEMASTER
/*@INVOICEID = (SELECT * FROM INVOICERETURN WHERE INVDATE=@INVOIC EDATE AND INVNO=@INVOICEN O AND INVTOTAL=@INVOI CETOTAL)*/
INSERT INTO INVOICECHILD1(I NVOICEREF,MARKS ANDCONTAINERSNO ,NUMBEROFPKGS,D ESCRIPTIONOFGOO DS,THICKNESS,CO LOUR,SELECTION, SIZE,UOM,CURREN CYDESC)
VALUES (@INVOICEID,@MA RKSANDCONTAINER SNO,@NUMBEROFPK GS,@DESCRIPTION OFGOODS,@THICKN ESS,@COLOUR,@SE LECTION,@SIZE,@ UOM,@CURRENCYDE SC)
COMMIT TRANSACTION
GO
The problem with above query is some data type conversion exist.. what exactly is that,i cant find out??
Any help would ease my development work!!!
Thanks in Advance..