Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sql dynamic
Message
 
 
To
17/10/2011 17:55:44
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Environment versions
Visual FoxPro:
VFP 9 SP1
OS:
Windows XP SP2
Network:
Windows 2003 Server
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01526661
Message ID:
01526701
Views:
35
There are a few problems in this SP, so please see my fixes:
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:		<jose manuel ramirez>
-- Create date: <17-10-2011>
-- =============================================
CRETAE PROCEDURE [dbo].[P_ANALISIS]@fecha1 varchar(10),@fecha2 varchar(10),@escolar int,@tipmov varchar(2),@nivel int,@dias int,@cuota int
AS
BEGIN
DECLARE @ParmDefinition NVARCHAR(2000),@condi varchar(2000),@sql nvarchar(4000)
SET @ParmDefinition = '@fecha1 varchar(10),@fecha2 varchar(10),@escolar int,@tipmov char(2),@nivel int,@dias int,@cuota int'

set @sql='select rtrim(F.apellidos) as apellidos,
SUM(M.balance) as balance,SUM(M.cuota) as monto,SUM(m.recargo) as recargo,0 as anticipo,
descuentos=sum(CASE WHEN (@fecha1>m.fechadescuento) THEN 0 else (M.descuento+M.descuento2) END),
dias30=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)<=30 and @fecha1>=m.fechacuota) THEN m.balance else 0 END),
dias45=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 30 and  datediff(dd,m.fechacuota,@fecha1)<= 45)  THEN m.balance else 0 END),
dias60=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 45 and  datediff(dd,m.fechacuota,@fecha1)<= 60)  THEN m.balance else 0 END),
dias75=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 60 and  datediff(dd,m.fechacuota,@fecha1)<= 75)  THEN m.balance else 0 END),
dias90=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 75 and  datediff(dd,m.fechacuota,@fecha1)<= 90)  THEN m.balance else 0 END),
dias91=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)>=91) THEN m.balance else 0 END)
from cuotas m,familias f 
where M.idfamilia=f.idfamilia and M.balance>0 '

IF @fecha1 IS NOT NULL and LEN(@Fetcha1) > 0
        SET @condi = @condi +'   AND m.fechacuota>= @fecha1'

IF @fecha2 IS NOT NULL and len(@Fetcha1) > 0
        SET @condi = @condi +'   AND m.fechacuota<= @fecha2'

IF @escolar <> 0  and @escolar IS NOT NULL
        SET @condi = @condi +'   AND m.escolar= @escolar'

IF @tipmov != ''
        SET @condi = @condi +'   AND m.tiptra= @tipmov'

IF @nivel <>0 and @Nivel IS NOT NULL
        SET @condi = @condi +'   AND m.nivel= @nivel'

IF @dias <>0 and @dias IS NOT NULL
        SET @condi = @condi +'   AND m.dias= @dias'

IF @cuota <>0 and @cuota IS NOT NULL
        SET @cuota = @condi +'   AND m.cuota= @cuota'


set @sql=@sql+' 
' + @condi + '
group by M.idfamilia,F.apellidos'

EXECUTE sp_executesql @sql,@ParmDefinition,@fecha1,@fecha2,@escolar,@tipmov,@nivel,@dias,@cuota
>WHY THIS STORE PROCEDURE I DO NOT RETURN DATA AS YOU PASS PARAMETER'''20111017 ', '2011 ','','','' IF THERE DATA
>
>
>set ANSI_NULLS ON
>set QUOTED_IDENTIFIER ON
>go
>-- =============================================
>-- Author:		<jose manuel ramirez>
>-- Create date: <17-10-2011>
>-- =============================================
>CRETAE PROCEDURE [dbo].[P_ANALISIS]@fecha1 varchar(10),@fecha2 varchar(10),@escolar int,@tipmov varchar(2),@nivel int,@dias int,@cuota int
>AS
>BEGIN
>DECLARE @ParmDefinition NVARCHAR(2000),@condi varchar(2000),@sql nvarchar(4000)
>SET @ParmDefinition = '@fech1 varchar(10),@fech2 varchar(10),@escola int,@tipmo char(2),@nive int,@dia int,@cuot int'
>
>set @sql='select rtrim(F.apellidos) as apellidos,
>SUM(M.balance) as balance,SUM(M.cuota) as monto,SUM(m.recargo) as recargo,0 as anticipo,
>descuentos=sum(CASE WHEN (@fecha1>m.fechadescuento) THEN 0 else (M.descuento+M.descuento2) END),
>dias30=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)<=30 and @fecha1>=m.fechacuota) THEN m.balance else 0 END),
>dias45=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 30 and  datediff(dd,m.fechacuota,@fecha1)<= 45)  THEN m.balance else 0 END),
>dias60=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 45 and  datediff(dd,m.fechacuota,@fecha1)<= 60)  THEN m.balance else 0 END),
>dias75=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 60 and  datediff(dd,m.fechacuota,@fecha1)<= 75)  THEN m.balance else 0 END),
>dias90=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)> 75 and  datediff(dd,m.fechacuota,@fecha1)<= 90)  THEN m.balance else 0 END),
>dias91=sum(CASE WHEN (datediff(dd,m.fechacuota,@fecha1)>=91) THEN m.balance else 0 END)
>from cuotas m,familias f 
>where M.idfamilia=f.idfamilia and M.balance>0 group by M.idfamilia,F.apellidos'
>
>IF @fecha1 != ''
>        SET @condi = @condi +'   AND m.fechacuota>= @fecha1'
>
>IF @fecha2 != ''
>        SET @condi = @condi +'   AND m.fechacuota<= @fecha2'
>
>IF @escolar != ''
>        SET @condi = @condi +'   AND m.escolar= @escolar'
>
>IF @tipmov != ''
>        SET @condi = @condi +'   AND m.tiptra= @tipmov'
>
>IF @nivel != ''
>        SET @condi = @condi +'   AND m.escolar= @escolar'
>
>IF @dias != ''
>        SET @condi = @condi +'   AND m.escolar= @escolar'
>
>IF @cuota != ''
>        SET @cuota = @condi +'   AND m.escolar= @escolar'
>
>
>set @sql=@sql+@condi
>
>EXECUTE sp_executesql @sql,@ParmDefinition,@fecha1,@fecha2,@escolar,@tipmov,@nivel,@dias,@cuota
>
>
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform