Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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

Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:06 AM.


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