View Single Post
  #2 (permalink)  
Old January 1st, 2008, 12:18 AM
urtrivedi urtrivedi is offline
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