Please Help, Nested stored proc is inconsitent 
Author Message
 Please Help, Nested stored proc is inconsitent

Hi, I am perplexed by the behavour of a set of stored procs. On one machine
these run fast and correctly, on another they run slow and return column
headings but no data. The development Server is  a 366 w/128megs of RAM, NT
4 SP5, SQL 7 SP1. The production box is a Dual 400 w/1gig of RAM, NT 4 SP5,
SQL 7 SP1.
The data on the development box is a copy of production using sp_attach_db.
On the development box the procs run correctly and return the expected
result, on production no joy.  The query plans that generated are different
for the same query on each box. If someone has an answer for this it would
save my sanity.
TIA, Bob





 AS
 SET CONCAT_NULL_YIELDS_NULL  OFF
-- We need to set wildcarded variables and then call pr_P2KMemberLookup in
order  to generate the correct query plan








varchar(30) ,

WITH RECOMPILE
AS

BEGIN
SELECT indvl_stakhldr.indvl_stakhldr_id,
               member.mbr_legacy_id,
              indvl_stakhldr.last_name,
              indvl_stakhldr.first_name ,
              indvl_stakhldr.mid_init ,
              indvl_stakhldr.ssn,
              indvl_stakhldr.sex,
              indvl_stakhldr.dob,
              indvl_stakhldr.last_mdfctn_user,
              indvl_stakhldr.last_mdfctn_date,
              tbl_individual_plan_emp.ipe_employer_no,
              tbl_employer.emp_employer_no

FROM member  INNER JOIN indvl_stakhldr ON member.mbr_stakhldr_id =
indvl_stakhldr.indvl_stakhldr_id
                          INNER JOIN tbl_individual_plan_emp ON
member.mbr_stakhldr_id = tbl_individual_plan_emp.ipe_stakhldr_id

AND member.fund_id = tbl_individual_plan_emp.ipe_subplan_id
                          INNER JOIN tbl_employer ON
tbl_individual_plan_emp.ipe_employer_no = tbl_employer.emp_employer_no




                UNION
  /* Get stakhldrs that do not have employment records.*/
  SELECT   indvl_stakhldr.indvl_stakhldr_id,
      Member.mbr_legacy_id,
      indvl_stakhldr.last_name ,
      indvl_stakhldr.first_name,
      indvl_stakhldr.mid_init,
      indvl_stakhldr.ssn,
      indvl_stakhldr.sex,
      indvl_stakhldr.dob,
      CONVERT(char(10), indvl_stakhldr.last_mdfctn_date, 101)
last_mdfctn_date,
      indvl_stakhldr.last_mdfctn_user,
      'emp_name',
                                           'emp_employer_no'

  FROM indvl_stakhldr  INNER  JOIN Member Member ON
indvl_stakhldr.indvl_stakhldr_id = Member.mbr_stakhldr_id

    indvl_stakhldr.indvl_stakhldr_id NOT IN(SELECT
indvl_stakhldr.indvl_stakhldr_id

FROM member INNER JOIN indvl_stakhldr ON member.mbr_stakhldr_id =
indvl_stakhldr.indvl_stakhldr_id

INNER JOIN tbl_individual_plan_emp ON member.mbr_stakhldr_id =
tbl_individual_plan_emp.ipe_stakhldr_id

AND member.fund_id = tbl_individual_plan_emp.ipe_subplan_id

INNER JOIN tbl_employer ON tbl_individual_plan_emp.ipe_employer_no =
tbl_employer.emp_employer_no








           ORDER BY indvl_stakhldr.Last_Name, indvl_stakhldr.First_Name,
indvl_stakhldr.mid_init
END
ELSE
BEGIN
SELECT  indvl_stakhldr.indvl_stakhldr_id,
                member.mbr_legacy_id,
                indvl_stakhldr.last_name,
                indvl_stakhldr.first_name,
                indvl_stakhldr.mid_init ,
                indvl_stakhldr.ssn,
                indvl_stakhldr.sex,
                indvl_stakhldr.dob,
                indvl_stakhldr.last_mdfctn_user,
                indvl_stakhldr.last_mdfctn_date,
                tbl_individual_plan_emp.ipe_employer_no,
                tbl_employer.emp_employer_no
FROM member  INNER JOIN indvl_stakhldr ON member.mbr_stakhldr_id =
indvl_stakhldr.indvl_stakhldr_id
                          INNER JOIN tbl_individual_plan_emp ON
member.mbr_stakhldr_id = tbl_individual_plan_emp.ipe_stakhldr_id

AND member.fund_id = tbl_individual_plan_emp.ipe_subplan_id
                          INNER JOIN tbl_employer ON
tbl_individual_plan_emp.ipe_employer_no = tbl_employer.emp_employer_no



                UNION
  /* Get stakhldrs that do not have employment records.*/
  SELECT  indvl_stakhldr.indvl_stakhldr_id,
     Member.mbr_legacy_id,
     indvl_stakhldr.last_name ,
     indvl_stakhldr.first_name,
     indvl_stakhldr.mid_init ,
     indvl_stakhldr.ssn,
     indvl_stakhldr.sex,
     indvl_stakhldr.dob,
     CONVERT(char(10), indvl_stakhldr.last_mdfctn_date, 101)
last_mdfctn_date,
     indvl_stakhldr.last_mdfctn_user,
    'emp_name', 'emp_employer_no'

  FROM indvl_stakhldr  INNER  JOIN Member Member ON
indvl_stakhldr.indvl_stakhldr_id = Member.mbr_stakhldr_id
  WHERE indvl_stakhldr.indvl_stakhldr_id NOT IN(SELECT
indvl_stakhldr.indvl_stakhldr_id
                                                      FROM member INNER JOIN
indvl_stakhldr ON member.mbr_stakhldr_id = indvl_stakhldr.indvl_stakhldr_id
                                                                  INNER JOIN
tbl_individual_plan_emp ON member.mbr_stakhldr_id =
tbl_individual_plan_emp.ipe_stakhldr_id
                                                                  AND
member.fund_id = tbl_individual_plan_emp.ipe_subplan_id
                                                                  INNER JOIN
tbl_employer ON tbl_individual_plan_emp.ipe_employer_no =
tbl_employer.emp_employer_no
                                                      WHERE






END



Fri, 28 Jun 2002 03:00:00 GMT
 Please Help, Nested stored proc is inconsitent

Try RTRIMing your variables before you post-pend the '%' onto them?


Quote:
> Hi, I am perplexed by the behavour of a set of stored procs. On one
machine
> these run fast and correctly, on another they run slow and return column
> headings but no data. The development Server is  a 366 w/128megs of RAM,
NT
> 4 SP5, SQL 7 SP1. The production box is a Dual 400 w/1gig of RAM, NT 4
SP5,
> SQL 7 SP1.
> The data on the development box is a copy of production using
sp_attach_db.
> On the development box the procs run correctly and return the expected
> result, on production no joy.  The query plans that generated are
different
> for the same query on each box. If someone has an answer for this it would
> save my sanity.
> TIA, Bob





>  AS
>  SET CONCAT_NULL_YIELDS_NULL  OFF
> -- We need to set wildcarded variables and then call pr_P2KMemberLookup in
> order  to generate the correct query plan








> varchar(30) ,


> WITH RECOMPILE
> AS

> BEGIN
> SELECT indvl_stakhldr.indvl_stakhldr_id,
>                member.mbr_legacy_id,
>               indvl_stakhldr.last_name,
>               indvl_stakhldr.first_name ,
>               indvl_stakhldr.mid_init ,
>               indvl_stakhldr.ssn,
>               indvl_stakhldr.sex,
>               indvl_stakhldr.dob,
>               indvl_stakhldr.last_mdfctn_user,
>               indvl_stakhldr.last_mdfctn_date,
>               tbl_individual_plan_emp.ipe_employer_no,
>               tbl_employer.emp_employer_no

> FROM member  INNER JOIN indvl_stakhldr ON member.mbr_stakhldr_id =
> indvl_stakhldr.indvl_stakhldr_id
>                           INNER JOIN tbl_individual_plan_emp ON
> member.mbr_stakhldr_id = tbl_individual_plan_emp.ipe_stakhldr_id

> AND member.fund_id = tbl_individual_plan_emp.ipe_subplan_id
>                           INNER JOIN tbl_employer ON
> tbl_individual_plan_emp.ipe_employer_no = tbl_employer.emp_employer_no




>                 UNION
>   /* Get stakhldrs that do not have employment records.*/
>   SELECT   indvl_stakhldr.indvl_stakhldr_id,
>       Member.mbr_legacy_id,
>       indvl_stakhldr.last_name ,
>       indvl_stakhldr.first_name,
>       indvl_stakhldr.mid_init,
>       indvl_stakhldr.ssn,
>       indvl_stakhldr.sex,
>       indvl_stakhldr.dob,
>       CONVERT(char(10), indvl_stakhldr.last_mdfctn_date, 101)
> last_mdfctn_date,
>       indvl_stakhldr.last_mdfctn_user,
>       'emp_name',
>                                            'emp_employer_no'

>   FROM indvl_stakhldr  INNER  JOIN Member Member ON
> indvl_stakhldr.indvl_stakhldr_id = Member.mbr_stakhldr_id

>     indvl_stakhldr.indvl_stakhldr_id NOT IN(SELECT
> indvl_stakhldr.indvl_stakhldr_id

> FROM member INNER JOIN indvl_stakhldr ON member.mbr_stakhldr_id =
> indvl_stakhldr.indvl_stakhldr_id

> INNER JOIN tbl_individual_plan_emp ON member.mbr_stakhldr_id =
> tbl_individual_plan_emp.ipe_stakhldr_id

> AND member.fund_id = tbl_individual_plan_emp.ipe_subplan_id

> INNER JOIN tbl_employer ON tbl_individual_plan_emp.ipe_employer_no =
> tbl_employer.emp_employer_no








>            ORDER BY indvl_stakhldr.Last_Name, indvl_stakhldr.First_Name,
> indvl_stakhldr.mid_init
> END
> ELSE
> BEGIN
> SELECT  indvl_stakhldr.indvl_stakhldr_id,
>                 member.mbr_legacy_id,
>                 indvl_stakhldr.last_name,
>                 indvl_stakhldr.first_name,
>                 indvl_stakhldr.mid_init ,
>                 indvl_stakhldr.ssn,
>                 indvl_stakhldr.sex,
>                 indvl_stakhldr.dob,
>                 indvl_stakhldr.last_mdfctn_user,
>                 indvl_stakhldr.last_mdfctn_date,
>                 tbl_individual_plan_emp.ipe_employer_no,
>                 tbl_employer.emp_employer_no
> FROM member  INNER JOIN indvl_stakhldr ON member.mbr_stakhldr_id =
> indvl_stakhldr.indvl_stakhldr_id
>                           INNER JOIN tbl_individual_plan_emp ON
> member.mbr_stakhldr_id = tbl_individual_plan_emp.ipe_stakhldr_id

> AND member.fund_id = tbl_individual_plan_emp.ipe_subplan_id
>                           INNER JOIN tbl_employer ON
> tbl_individual_plan_emp.ipe_employer_no = tbl_employer.emp_employer_no



>                 UNION
>   /* Get stakhldrs that do not have employment records.*/
>   SELECT  indvl_stakhldr.indvl_stakhldr_id,
>      Member.mbr_legacy_id,
>      indvl_stakhldr.last_name ,
>      indvl_stakhldr.first_name,
>      indvl_stakhldr.mid_init ,
>      indvl_stakhldr.ssn,
>      indvl_stakhldr.sex,
>      indvl_stakhldr.dob,
>      CONVERT(char(10), indvl_stakhldr.last_mdfctn_date, 101)
> last_mdfctn_date,
>      indvl_stakhldr.last_mdfctn_user,
>     'emp_name', 'emp_employer_no'

>   FROM indvl_stakhldr  INNER  JOIN Member Member ON
> indvl_stakhldr.indvl_stakhldr_id = Member.mbr_stakhldr_id
>   WHERE indvl_stakhldr.indvl_stakhldr_id NOT IN(SELECT
> indvl_stakhldr.indvl_stakhldr_id
>                                                       FROM member INNER
JOIN
> indvl_stakhldr ON member.mbr_stakhldr_id =

indvl_stakhldr.indvl_stakhldr_id

- Show quoted text -

Quote:
>                                                                   INNER
JOIN
> tbl_individual_plan_emp ON member.mbr_stakhldr_id =
> tbl_individual_plan_emp.ipe_stakhldr_id
>                                                                   AND
> member.fund_id = tbl_individual_plan_emp.ipe_subplan_id
>                                                                   INNER
JOIN
> tbl_employer ON tbl_individual_plan_emp.ipe_employer_no =
> tbl_employer.emp_employer_no
>                                                       WHERE





>                                      indvl_stakhldr.first_name LIKE

> END



Sat, 29 Jun 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Help calling nested Stored Proc from ASP

2. Rookie: Nested Stored Procedures - please help

3. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

4. Query/View/Stored Proc structure - please help!?

5. !!!! PLEASE HELP ::stored proc call dies with ODBC

6. please help stored proc issue

7. PLEASE HELP - stored proc

8. Stored Proc Date conversion Problem, Please Help

9. -----need help with stored proc---PLEASE

10. Please Help with Stored Proc

11. calling Stored Proc with InputOutput params - help please!

12. ADO Stored Proc...AAAAAAAAAAAARRG please help


 
Powered by phpBB® Forum Software