Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    4

    Unanswered: problem with procedure

    Hi, Please with procedure:

    go

    --PROCEDIMIENTO CARGAR DM_CURSOS

    create procedure dbo.sp_dm_cursos @db varchar(50) as


    INSERT INTO [DW_MMQ].[dbo].[dm_cursos]
    ([cu_codigo], [cu_descripcion], [cu_cod_nivel],
    [cu_des_nivel], [cu_cod_paralelo], [cu_des_paralelo],
    [cu_año_lectivo], [cu_cod_unidad])

    select
    convert(varchar,a.courseid) + 'Sec' as codigo,
    case b.name
    when 'Basica' then 'Básica'
    else b.name end as nombre ,
    d.levelid as cod_nivel,
    d.name as nom_nivel,
    c.parallelid as cod_paralelo,
    c.name as nom_paralelo,
    convert(varchar,startrange)+ '-'+ convert(varchar,endrange) as cod_año_lectivo,
    1 as cod_unidad
    from
    [@db].[dbo].[Course] a,
    [@db].[dbo].[Parallel] c,
    [@db].[dbo].[mLevel] d,
    [@db].[dbo].[Specialization] b,
    [@db].[dbo].[SchoolYear] e

    where a.parallelid = c.parallelid
    and a.levelid = d.levelid
    and b.SpecializationID = d.SpecializationID
    and e.schoolyearid = c.schoolyearid
    and b.schoolyearid = e.schoolyearid
    and convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

    -- execute sp_dm_cursos2 'Quitumbe'
    --this is the problem?, please
    Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
    Invalid object name '@db.dbo.Course'.
    Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
    Invalid object name '@db.dbo.Parallel'.
    Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
    Invalid object name '@db.dbo.mLevel'.
    Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
    Invalid object name '@db.dbo.Specialization'.
    Server: Msg 208, Level 16, State 1, Procedure sp_dm_cursos2, Line 20
    Invalid object name '@db.dbo.SchoolYear'.
    Last edited by miguelmrj45; 01-26-05 at 17:05. Reason: mal escrito

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    mi espanol is muy mal. que es el problemo? necessito mas informacion. Inglis por favor?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Start by converting your WHERE syntax into the more acceptable JOIN syntax:

    from
    [Quitumbe_Secundaria].[dbo].[Course] a
    inner join [Quitumbe_Secundaria].[dbo].[Parallel] c on a.parallelid = c.parallelid
    inner join [Quitumbe_Secundaria].[dbo].[mLevel] d on a.levelid = d.levelid
    inner join [Quitumbe_Secundaria].[dbo].[Specialization] b on b.SpecializationID = d.SpecializationID
    inner join [Quitumbe_Secundaria].[dbo].[SchoolYear] e
    on e.schoolyearid = c.schoolyearid
    and e.schoolyearid = b.schoolyearid
    where convert(varchar,a.courseid) + 'Sec' not in (select cu_codigo from dm_cursos)

    Now, if you map out your table relationships, you can see that you have exclusive inner joins for five tables the form a relational loop:

    Code:
    A - C \
    |      E
    D - B /
    For a record to appear in your dataset, all five of these joins must be satisfied. It is very possible that you have no records that pass this test, plus the criteria left in the WHERE clause above.

    You may be able to drop [SchoolYear] from your query entirely, as it does not seem to appear in the SELECT clause, and tables [Parallel] and [Specialization] can be joined directly on the shared schoolyearid key.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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