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.One procedure = One output set = Good Programming.|||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.|||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|||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.