Create Store Procedure ??

kumiko · December 31st, 2007, 10:57 AM

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

urtrivedi · January 1st, 2008, 12:18 AM

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

urtrivedi · January 1st, 2008, 12:20 AM

if you are using mssql, then post your questions at sql server 2000/2005 forum.

urt

urtrivedi · January 1st, 2008, 12:23 AM

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

urtrivedi · January 1st, 2008, 12:25 AM

INSERT INTO SECOND_TABLE (PK_COL,OTHER_COL1,OTHER_COL2) VALUES (@PK_COL,@OTHER_COL1,@OTHER_COL2)

urt

kumiko · January 3rd, 2008, 11:22 PM

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

urtrivedi · January 4th, 2008, 01:28 AM

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

kumiko · January 4th, 2008, 02:11 AM

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