Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old December 31st, 2007, 10:57 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

  #2 (permalink)  
Old January 1st, 2008, 12:18 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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
  #3 (permalink)  
Old January 1st, 2008, 12:20 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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

urt
  #4 (permalink)  
Old January 1st, 2008, 12:23 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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
  #5 (permalink)  
Old January 1st, 2008, 12:25 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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


urt
  #6 (permalink)  
Old January 3rd, 2008, 11:22 PM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old January 4th, 2008, 01:28 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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
  #8 (permalink)  
Old January 4th, 2008, 02:11 AM
Authorized User
 
Join Date: Aug 2006
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Similar Threads
Thread Thread Starter Forum Replies Last Post
Store procedure help... RinoDM SQL Server 2000 7 August 11th, 2008 07:09 PM
Store procedure help ??? RinoDM SQL Server 2000 8 May 1st, 2008 03:03 PM
store procedure if else problem krshekhar SQL Language 0 February 20th, 2008 05:31 AM
Store Procedure sureshyuga SQL Server 2000 0 May 18th, 2007 01:49 AM
Create a new user using store procedure. vaidya_kaustubh BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 1 April 30th, 2007 07:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.