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

    Unanswered: problem with procedure 2

    Hi, Please with procedure not funcion:

    --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)

    RESULT
    -- 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'.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    change

    [@db].[dbo].[Course] a,
    [@db].[dbo].[Parallel] c,
    [@db].[dbo].[mLevel] d,
    [@db].[dbo].[Specialization] b,
    [@db].[dbo].[SchoolYear] e

    to

    [db].[dbo].[Course] a,
    [db].[dbo].[Parallel] c,
    [db].[dbo].[mLevel] d,
    [db].[dbo].[Specialization] b,
    [db].[dbo].[SchoolYear] e
    “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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    create procedure dbo.sp_dm_cursos @db varchar(50) as
    
    DECLARE @sql varchar(8000)
    
    SELECT @sql = '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)'
    
    SELECT @sql
    
    EXEC(@sql)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope..the db is dynamic

    Quote Originally Posted by Thrasymachus
    change

    [@db].[dbo].[Course] a,
    [@db].[dbo].[Parallel] c,
    [@db].[dbo].[mLevel] d,
    [@db].[dbo].[Specialization] b,
    [@db].[dbo].[SchoolYear] e

    to

    [db].[dbo].[Course] a,
    [db].[dbo].[Parallel] c,
    [db].[dbo].[mLevel] d,
    [db].[dbo].[Specialization] b,
    [db].[dbo].[SchoolYear] e
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2005
    Posts
    4

    thank

    thank brett

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No worries...but why do you have to do it that way?

    How many databases do you have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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