Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: Problem With Using Stored Procs As I/p To Another Stored Procs

    HI,

    CREATE PROCEDURE PROC1
    AS
    BEGIN
    SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
    FROM CUSTOMER A
    INNER JOIN CUSTOMERPREFERENCE B
    ON A.INTCUSTOMERID = B.INTCUSTOMERID
    INNER JOIN TMPREFERENCE C
    ON B.INTPREFERENCEID = C.INTPREFERENCEID
    WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
    ORDER BY B.INTCUSTOMERID

    END

    IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

    CREATE PROCEDURE PROC2
    AS
    BEGIN

    CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

    INSERT INTO #SAATHI
    EXEC PROC1


    ST......1,
    ST......2,


    END.

    BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


    CREATE PROCEDURE MY_PROC
    AS
    BEGIN
    SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
    FROM CUSTOMER A
    INNER JOIN CUSTOMERPREFERENCE B
    ON A.INTCUSTOMERID = B.INTCUSTOMERID
    INNER JOIN TMPREFERENCE C
    ON B.INTPREFERENCEID = C.INTPREFERENCEID
    WHERE B.INTPREFERENCEID IN (23,12,10)
    ORDER BY B.INTCUSTOMERID

    END

    SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
    else '0' end) +
    MAX(case when A.intpreferenceid = 12 then '1'
    else '0' end) +
    MAX(case when A.intpreferenceid = 10 then '1'
    else '0' end) AS PREFER
    FROM CUSTOMER
    GROUP BY A.INTCUSTOMERID
    ORDER BY A.INTCUSTOMERID
    END

    WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



    CREATE PROCEDURE PROC2
    AS
    BEGIN

    CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

    INSERT INTO #SAATHI
    EXEC MY_PROC


    ST......1,
    ST......2,

    END.

    BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

    THANKS.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One procedure = One output set = Good Programming.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Posts
    49
    Hi,

    Seems To Be An Ideal Programming Environment ,but Not Practical
    When You Are Dealing With A Huge Database,several Hundred Procs And Many Thousand Computations To Get The Result.

    Not Possible To Write A Proc For Each Process That Is Been Done.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by C_BHANDARY
    Not Possible To Write A Proc For Each Process That Is Been Done.
    Yes, it is possible. As a matter of fact, it is the only way I know to survive as the database complexity increases.

    I've worked in "spaghetti shops" like you describe, where there are a few thousand routines that do many thousand things. Nothing is ever quite "plug and play" because everything has a side-effect. Finally the people that work there get tired of working 60-80 hours per week, especially when they realize that other folks doing more real work only have to work 45-50 hours per week.

    At some point you'll get tired of rigging things with string and bailing wire, and will try a project or two using a more disciplined methodology. One routine does exactly one well defined task. Once you see how much easier that makes your job, there is no going back to the familiar chaos that is your spaghetti code!

    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    49

    Smile

    Hi,
    Thanks Pat,i Really Think I Am Getting The Points Now .and I Think I Have To Bring In More Discipline In My Programming And Towards Its' Approach.
    Ok !!! Think,it's Time To Break Down Most Of The Routines Into Simple Steps.
    Thanks,to You I Am Realizing That There Is No Point In Making It Happen All Once, It Would Rather Be One At A Time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •