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_ao_lectivo], [cu_cod_unidad])

select
convert(varchar,a.courseid) + 'Sec' as codigo,
case b.name
when 'Basica' then 'Bsica'
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_ao_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'.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|||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_ao_lectivo], [cu_cod_unidad]) '
+''
+'select '
+'convert(varchar,a.courseid) + ''Sec'' as codigo,'
+'case b.name '
+'when ''Basica'' then ''Bsica'' '
+'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_ao_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)|||Nope..the db is dynamic

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|||thank brett|||No worries...but why do you have to do it that way?

How many databases do you have?

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_ao_lectivo], [cu_cod_unidad])

select
convert(varchar,a.courseid) + 'Sec' as codigo,
case b.name
when 'Basica' then 'Bsica'
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_ao_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'.mi espanol is muy mal. que es el problemo? necessito mas informacion. Inglis por favor?|||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:

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.