June 23, 2012 at 10:07 am
Hi Guys,
I have this procedure:
USE [SGSC]
GO
/****** Object: StoredProcedure [dbo].[spSGCT_VALIDA_SGCTLocais] Script Date: 06/25/2012 17:00:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSGCT_VALIDA_SGCTLocais] (@DB as varchar(50))
AS
BEGIN
set nocount on
delete from FicheirosImportar where sessaoinicio <> sessaofim and sessaoinicio <> 'T'
DECLARE
@MaxTotalidade as BIGINT,
@MaxDiferenca as BIGINT,
@VAL1 as INT,
@Inicio as varchar(10),
@fim as varchar(10),
@CODRF as varchar(10),
@SQLString NVARCHAR(4000),
@ParmDefinition NVARCHAR(500),
@MinVal bigint
DECLARE db_cursor CURSOR FOR
select distinct(codrf) from FicheirosImportar
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @codrf
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'select @maxVal = isnull(max(cod_sincronismo_fim),0)
from '+@DB+'.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2 AND STATUS =0
and cod_rep_fiscal ='''+@codrf+''''
SET @ParmDefinition = '@maxVal bigint OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @maxVal = @MaxTotalidade OUTPUT
SET @SQLString = 'select @MinVal = isnull(max(cod_sincronismo_fim),0)
from '+@DB+'.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =1 AND STATUS =0
and cod_rep_fiscal ='''+@codrf+''''
SET @ParmDefinition = '@MinVal bigint OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @MinVal = @MaxDiferenca OUTPUT
IF (select count(sessaofim) from FicheirosImportar where
sessaoinicio ='T' and sessaofim > @MaxTotalidade
and sessaofim > @MaxDiferenca and codrf =''+@codrf+'') <> 0
BEGIN
SET @MaxTotalidade = (select isnull(max(sessaofim),0) from FicheirosImportar where
sessaoinicio ='T' and sessaofim > @MaxTotalidade
and sessaofim > @MaxDiferenca and codrf =''+@codrf+'')
UPDATE FicheirosImportar set valido =1 where codrf = ''+@codrf+''
and sessaoinicio ='T' and sessaofim = @MaxTotalidade
END
IF (@MaxTotalidade > @MaxDiferenca)
BEGIN
IF (select count(sessaofim) from FicheirosImportar where
sessaoinicio <> 'T' and sessaofim > @MaxTotalidade and codrf =''+@codrf+'') <> 0
UPDATE FicheirosImportar set valido =1 where
sessaoinicio <> 'T' and sessaofim > @MaxTotalidade and codrf =''+@codrf+''
END
ELSE
SET @SQLString = ('select @val = count(sessaofim)
from
FicheirosImportar
where
sessaoinicio <> ''T'' and sessaofim > '+CONVERT(VARCHAR,@MaxTotalidade)+'
and
codrf ='''+@codrf+'''
and (sessaofim > '+CONVERT(VARCHAR,@MaxDiferenca)+'
or
sessaofim not in
(select cod_sincronismo_fim
from
'+@db+'.dbo.sincronismo
where
cod_rep_fiscal ='''+@codrf+'''
and
cod_tp_classificacao =1
and
cod_sincronismo_fim > '+CONVERT(VARCHAR,@MaxTotalidade)+'))')
SET @ParmDefinition = '@Val VARCHAR(5) OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @val = @VAL1 OUTPUT
IF (@VAL1 <> 0)
BEGIN
SET @SQLString ='update FicheirosImportar
set valido =1
where
sessaoinicio <> ''T'' and sessaofim > '+CONVERT(VARCHAR,@MaxTotalidade)+'
and
codrf ='''+@codrf+'''
and
(sessaofim > '+CONVERT(VARCHAR,@MaxDiferenca)+'
or
sessaofim not in( select cod_sincronismo_fim
from
'+@db+'.dbo.sincronismo
where
cod_rep_fiscal ='''+@codrf+'''
and
cod_tp_classificacao =1
and
cod_sincronismo_fim > '+CONVERT(VARCHAR,@MaxTotalidade)+'))'
EXEC (@SQLString)
END
UPDATE FicheirosImportar set ordem =1 where valido =1 and
sessaoinicio ='T' and codrf =''+@codrf+''
UPDATE ficheirosimportar set ordem = a.row +1
from
(select ROW_NUMBER() OVER( ORDER BY sessaoinicio) AS Row,codrf RF,
sessaoinicio SI,sessaofim SF
FROM
FicheirosImportar
WHERE
sessaoinicio <> 'T' and valido =1) a
where
ficheirosimportar.codrf = RF and ficheirosimportar.sessaoinicio = SI
and
ficheirosimportar.sessaofim =SF and RF = ''+@codrf+''
FETCH NEXT FROM db_cursor INTO @codrf
END
CLOSE db_cursor
DEALLOCATE db_cursor
IF (select count(*) from ficheirosimportar where valido =1) <> 0
BEGIN
RETURN 1
END
ELSE
RETURN 0
END
When I execute it, I receive the following error message:
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@MinVal".
Can someone help?
thanks.
P.S: If I execute like this:
alter proc teste
as
Begin
DECLARE
@MaxTotalidade as BIGINT,
@MaxDiferenca as BIGINT,
@VAL1 as INT,
@Inicio as varchar(10),
@fim as varchar(10),
@CODRF as varchar(10),
@SQLString NVARCHAR(4000),
@ParmDefinition NVARCHAR(500),
@db as varchar(50)
set @db ='SGCTCentral'
set @codrf ='4.01'
SET @SQLString = 'select @MinVal = isnull(max(cod_sincronismo_fim),0)
from '+@DB+'.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =1 AND STATUS =0
and cod_rep_fiscal ='''+@codrf+''''
SET @ParmDefinition = '@MinVal bigint OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @MinVal = @MaxDiferenca OUTPUT
print @maxdiferenca
end
exec teste
It works well and the returned value by the print function is:
1861
June 23, 2012 at 11:57 am
I'm sorry my thoughts were different
Igor Micev,My blog: www.igormicev.com
June 23, 2012 at 1:17 pm
IF I do it like you told:
Declare the variable I receive this error:
The variable name '@MinVal' has already been declared. Variable names must be unique within a query batch or stored procedure.
I need help on this....
Don't know what more to do...
June 23, 2012 at 1:30 pm
It seems like this happens only when the code enters in the condition:
IF (select count(sessaofim) from FicheirosImportar where
sessaoinicio ='T' and sessaofim > @MaxTotalidade
and sessaofim > @MaxDiferenca and codrf =''+@codrf+'') <> 0
BEGIN
SET @MaxTotalidade = (select isnull(max(sessaofim),0) from FicheirosImportar where
sessaoinicio ='T' and sessaofim > @MaxTotalidade
and sessaofim > @MaxDiferenca and codrf =''+@codrf+'')
UPDATE FicheirosImportar set valido =1 where codrf = ''+@codrf+''
and sessaoinicio ='T' and sessaofim = @MaxTotalidade
END
June 23, 2012 at 1:33 pm
or this piece of code:
IF (@MaxTotalidade > @MaxDiferenca)
BEGIN
IF (select count(sessaofim) from FicheirosImportar where
sessaoinicio <> 'T' and sessaofim > @MaxTotalidade and codrf =''+@codrf+'') <> 0
UPDATE FicheirosImportar set valido =1 where
sessaoinicio <> 'T' and sessaofim > @MaxTotalidade and codrf =''+@codrf+''
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply