 |
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

December 31st, 2007, 10:57 AM
|
Authorized User
|
|
Join Date: Aug 2006
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Create Store Procedure ??
halo there...happy new year..i need some help on the store procedure in MS SQL,i m quite new in SP..i cant find any example of tutorial on it..
i have two tables, that is table A and table B. and i got 10 records from the form need to insert into the seperate field for table A and B.
ok..wat i wan to do is:
1) : i need to select the max order number from table A. mean i have to select the last order number from the table A. let say, the last order number is 1000, then the new order number will be 1001( ordernum =ordernum+1)
2) GET the ordernumber and the 5 records from the form and then insert into the field for TABLE A.
3) then, SELECt the MAX ORDER ID from the table A(which is the PK) and then set as the variable.
4) Insert the MAX order ID into the table B with the other field records..
plz help..i duno how to write a SP about this..plz help...thanx
|

January 1st, 2008, 12:18 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
RIGHT CLICK ON STORED PROCEDURE IN ENTERPRISE MANAGER AND START WRITING FOLLOWING CODE
YOUR NEED TO CREATE TWO PROCEDURES
ONE FOR INSERT INTO FIRST_TABLE
ONE FOR INSERT INTO SECOND_TABLE
CREATE PROCEDURE INSERT_FIRST
(
@PK_COL INTEGER OUTPUT,
@OTHER_COL VARCHAR(25)
)
AS
SELECT @PK_COL =ISNULL(MAX(PK_COL),0)+1 FROM FIRST_TABLE
INSERT INTO FIRST_TABLE (PK_COL,OTHER_COL) VALUES(@PK_COL,@OTHER_COL)
----------SECOND
CREATE PROCEDURE INSERT_SECOND
(
@PK_COL INTEGER ,
@OTHER_COL1 VARCHAR(25)
@OTHER_COL2 VARCHAR(25)
)
AS
INSERT INTO FIRST_TABLE (PK_COL,OTHER_COL) VALUES (@PK_COL,@OTHER_COL1,@OTHER_COL2)
--HOW TO CALL FROM QUERY ANALYSER
DECLARE @PK_COL_TMP INTEGER
EXEC INSERT_FIRST @PK_COL=@PK_COL_TMP,@OTHER_COL='SOME VALUE'
--NOW @PK_COL_TMP WILL HAVE NEW GENERATED VALUE
EXEC INSERT_SECOND @PK_COL=@PK_COL_TMP,@OTHER_COL1='SOME VALUE1',@OTHER_COL2='SOME VALUE 2'
--TO CALL FROM FRONT END EVERY FRONT END HAS ITS OWN SET OF SYNTAX TO CALL STORED PROCEDURE AND TO RETRIVED OUTPUT TYPE OF
PARAMETER YOU HAVE TO REFER FRONT ENDS MANUAL
urt
|

January 1st, 2008, 12:20 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
if you are using mssql, then post your questions at sql server 2000/2005 forum.
urt
|

January 1st, 2008, 12:23 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
there is mistake in second procedure,
following is correct code
INSERT INTO FIRST_TABLE (PK_COL,OTHER_COL1,OTHER_COL2) VALUES (@PK_COL,@OTHER_COL1,@OTHER_COL2)
urt
|

January 1st, 2008, 12:25 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
INSERT INTO SECOND_TABLE (PK_COL,OTHER_COL1,OTHER_COL2) VALUES (@PK_COL,@OTHER_COL1,@OTHER_COL2)
urt
|

January 3rd, 2008, 11:22 PM
|
Authorized User
|
|
Join Date: Aug 2006
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Code:
Create Procedure NewOrder
@customerid NUMERIC(9),
@total FLOAT ,
@discountamount FLOAT,
@discountpercent FLOAT,
@Remarks VARCHAR(MAX),
@StatusOrder NCHAR(10),
@DateCreated datetime,
@datemodified datetime,
@CreatedBy numeric(9),
@ModifiedBy numeric(9),
@IsActive bit,
@DomainName nvarchar(300),
@Years int,
@BillToID numeric(9),
@BillType numeric(9),
@domaintype nvarchar(300),
@totaloriginal float,
@serialnum VARCHAR(MAX),
@productid numeric(9),
@quantity int,
@totalprice float,
@billingID numeric(9),
@promotionID numeric(9),
@promotionTypeID numeric(9),
@unitprice float
AS
BEGIN
DECLARE @counter int
DECLARE @ordernum numeric(9)
DECLARE @orderprefix nvarchar(300)
DECLARE @maxorderid NUMERIC(9)
SELECT @counter =Count(*) FROM TBL_Order
IF (@counter = 0)
BEGIN
SET @ordernum ='1000'
END
ELSE
BEGIN
SELECT @ordernum=MAX(ordernumber) FROM TBL_Order
SET @ordernum =@ordernum +1
/*SET @newordernum = @orderprefix + ' ' + @newordernum*/
END
IF (@ordernum IS NOT NULL)
BEGIN
INSERT INTO tbl_order(Ordernumber,OrderPrefix,CustomerMasterID,TotalAmount,DiscountAmount,DiscountPercentage,Remarks,StatusOrder,DateCreated,DateModified,CreatedBy,ModifiedBy,IsActive,DomainName,Years,BillToID,BillType,domaintype,TotalOriginal,SerialNum)
VALUES(@ordernum,@orderprefix,@customerid,@total ,@discountamount,@discountpercent,@Remarks,@StatusOrder,@DateCreated,@datemodified, @CreatedBy,@ModifiedBy, @IsActive,@DomainName,@Years, @BillToID,@BillType,@domaintype,@totaloriginal, @serialnum)
IF @@ERROR <>0
BEGIN
PRINT 'ERROR OCCURED'
END
SELECT @maxorderid = @@identity
INSERT INTO tbl_details(OrderMasterID,ProductMasterID,Quantity,Total,BillingCycleID,PromotionMasterID,PromotionTypeID,DateCreated,DateModified,CreatedBy,ModifiedBy,IsActive,UnitPrice)
VALUES(@maxorderid,@ProductID,@quantity,@totalprice,@billingID,@promotionID,@promotionTypeID,@DateCreated,@DateModified,@CreatedBy,@ModifiedBy,@Isactive,@unitprice)
IF @@ERROR <>0
BEGIN
PRINT 'ERROR OCCURED'
END
END
END
HALO THERE..I HAVE TRY TO WRITE A SP as above..but i duno hwo to add in the code transaciton,commit, rollback, and if got any error will return msg...can someone help me review my code? help me improve the sp..thanx alot
|

January 4th, 2008, 01:28 AM
|
Friend of Wrox
|
|
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
in the begining of procedure after keyword AS
BEGIN TRANSACTION
-YOUR CODE
-.....
IF (@@ERROR<>0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
Also you should write one stored procedure for each table. do not write everthing in one procedure. Error message will come to your front end code directly. Print command will not help it will show message only if you execute query in query analyser.
urt
|

January 4th, 2008, 02:11 AM
|
Authorized User
|
|
Join Date: Aug 2006
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
hello urt,
i m not understand wat u mean
Code:
Also you should write one stored procedure for each table.
do not write everthing in one procedure.
Error message will come to your front end code directly.
Print command will not help it will show message only if you execute query in query analyser.
plz give more detail guidance..coz...store procedure newbie.. thank you
|
|
 |