Thanks, for your help,
this is how my sp looks like:
CREATE PROCEDURE spRptOperOutstanding @fecha datetime, @tipo nvarchar(1)
AS
if exists (Select * from tblRptOperOutstandingAll) 'this is it.
drop table tblRptOperOutstandingAll
'
if month(@fecha)=01 or month(@fecha)=07
begin
Select vwRptOperPagoDeuda.ident as ident, vwRptOperPagoDeuda.municipio as municipio, vwRptOperPagoDeuda.secuencia as secuencia,
vwRptOperPagoDeuda.nombre as nombre,vwRptOperPagoDeuda.clcust as clcust, vwRptOperPagoDeuda.clnote as clnote,
vwRptOperPagoDeuda.cantidadoriginal as cantidadoriginal, vwRptOperPagoDeuda.sindesembolsar as sindesembolsar, vwRptOperPagoDeuda.varfijo as varfijo,
vwRptOperPagoDeuda.fechapago as fechapago, vwRptOperPagoDeuda.interestrate as interestrate, vwRptOperPagoDeuda.interestamount as interestamount,
vwRptOperPagoDeuda.principalpayment as principalpayment, vwRptOperPagoDeuda.balancebefpay as balancebefpay, vwRptOperPagoDeuda.syndicateidno as syndicateidno, vwRptOperPagoDeuda.agentidno as agentidno, tblOperPlanPago.intereses as intereses,
tblOperPlanPago.principal as principal, vwRptOperPagoDeuda.instname as instname, (vwRptOperPagoDeuda.interestamount + isnull(tblOperPlanPago.intereses,0)) as Inte,
(vwRptOperPagoDeuda.principalpayment + isnull(tblOperPlanPago.principal,0)) as Prin
into tblRptOperOutstandingAll
FROM vwRptOperPagoDeuda LEFT JOIN tblOperPlanPago ON (vwRptOperPagoDeuda.fechapago = tblOperPlanPago.fechapago)
AND (vwRptOperPagoDeuda.secuencia = tblOperPlanPago.secuencia) AND (vwRptOperPagoDeuda.municipio = tblOperPlanPago.municipio)
AND (vwRptOperPagoDeuda.ident = tblOperPlanPago.ident)
where vwRptOperPagoDeuda.ident=@tipo and ((vwRptOperPagoDeuda.fechapago=@fecha) or (vwRptOperPagoDeuda.metodopagointereses='2' and
vwRptOperPagoDeuda.fechapago=(dateadd(mm,3,@fecha))))
ORDER BY vwRptOperPagoDeuda.ident, vwRptOperPagoDeuda.municipio, vwRptOperPagoDeuda.secuencia,vwRptOperPagoDeuda.fechapago;
end
else
if month(@fecha)=04 or month(@fecha)=10
begin
Select vwRptOperPagoDeuda.ident as ident,vwRptOperPagoDeuda.municipio as municipio, vwRptOperPagoDeuda.secuencia as secuencia,
vwRptOperPagoDeuda.nombre as nombre,vwRptOperPagoDeuda.clcust as clcust, vwRptOperPagoDeuda.clnote as clnote,
vwRptOperPagoDeuda.cantidadoriginal as cantidadoriginal, vwRptOperPagoDeuda.sindesembolsar as sindesembolsar, vwRptOperPagoDeuda.varfijo as varfijo,
vwRptOperPagoDeuda.fechapago as fechapago, vwRptOperPagoDeuda.interestrate as interestrate, vwRptOperPagoDeuda.interestamount as interestamount,
vwRptOperPagoDeuda.principalpayment as principalpayment, vwRptOperPagoDeuda.balancebefpay as balancebefpay, vwRptOperPagoDeuda.syndicateidno as syndicateidno, vwRptOperPagoDeuda.agentidno as agentidno, tblOperPlanPago.intereses as intereses,
tblOperPlanPago.principal as principal, vwRptOperPagoDeuda.instname as instname, (vwRptOperPagoDeuda.interestamount + isnull(tblOperPlanPago.intereses,0)) as Inte,
(vwRptOperPagoDeuda.principalpayment + isnull(tblOperPlanPago.principal,0)) as Prin
into tblRptOperOutstandingAll
FROM vwRptOperPagoDeuda LEFT JOIN tblOperPlanPago ON (vwRptOperPagoDeuda.fechapago = tblOperPlanPago.fechapago)
AND (vwRptOperPagoDeuda.secuencia = tblOperPlanPago.secuencia) AND (vwRptOperPagoDeuda.municipio = tblOperPlanPago.municipio)
AND (vwRptOperPagoDeuda.ident = tblOperPlanPago.ident)
where vwRptOperPagoDeuda.ident=@tipo and ((vwRptOperPagoDeuda.metodopagointereses='3' and vwRptOperPagoDeuda.fechapago=@fecha)
or (vwRptOperPagoDeuda.metodopagointereses='2' and vwRptOperPagoDeuda.fechapago=(dateadd(mm,3,@fecha))))
ORDER BY vwRptOperPagoDeuda.ident, vwRptOperPagoDeuda.municipio, vwRptOperPagoDeuda.secuencia,vwRptOperPagoDeuda.fechapago;
end
return
GO
As a general rule you should not do ddl in an sp that also runs regular DML statements, put the ddl in another proc, this will generally lead to better compilation behaviour. Not sure what is up with your code, the follow works for me. keep executing the exec line to see the existance of the table change.
use tempdb
create table foo(
col1 int,
col2 nvarchar(10)
)
create procedure bar
as
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[foo]') and objectproperty(id, N'IsUserTable') = 1)
begin
print 'Does not exist'
create table foo(
col1 int,
col2 nvarchar(10)
)
end
else
begin
print 'Already exists'
drop table foo
end
return
go
exec bar
