June 4, 2012 at 8:29 am
I have this statement inside a stored procedure:
exec('set '+@MaxTotalidade+'=(select convert(bigint,isnull(max(cod_sincronismo_fim),0)) from SGCTCentral.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''''4.01''''')
When I run the code I keep receiving this message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '4.01'.
Can someone help?
Thanks
June 4, 2012 at 8:44 am
Looks like you have a misplaced quote at the end and you're missing a closing parenthesis.
June 4, 2012 at 8:46 am
Can you print command and try executing that in SSMS?
something likle this...
declare @cmd as varchar(max),@MaxTotalidade as ??
set @cmd ='set '+@MaxTotalidade+'=(select convert(bigint,isnull(max(cod_sincronismo_fim),0)) from SGCTCentral.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''''4.01'''''
print @cmd
Also you cannot set a variable using exec('') since the command will be executed in different session, you have to use sp_executesql with parameter(s) to get value outside
June 4, 2012 at 8:47 am
Yes.
Thanks.
But still I have a problem.
exec('set '+@MaxTotalidade+ ' =(select convert(bigint,isnull(max(cod_sincronismo_fim),0))
from SGCTCentral.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''4.01'')')
Is says this message:
Incorrect syntax near '='
Can you help?
Thank you
June 4, 2012 at 9:02 am
if I do like this:
set @cmd = ('set '+@MaxTotalidade+ ' = (select isnull(max(cod_sincronismo_fim),0) as maximo
from SGCTCentral.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''4.01'')')
print @cmd
select @cmd
The returned result is:
Null
June 4, 2012 at 9:04 am
If I add the execute command to the query, when I execute the procedure I receive the following:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Command:
exec('set '+@MaxTotalidade+ ' = (select isnull(max(cod_sincronismo_fim),0) as maximo
from SGCTCentral.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''4.01'')')
June 4, 2012 at 9:09 am
As I have mentioned this in my first comment,
you cannot set a variable using exec('') since the command will be executed in different session, you have to use sp_executesql with parameter(s) to get value outside
June 4, 2012 at 9:13 am
Daxesh Patel (6/4/2012)
As I have mentioned this in my first comment,you cannot set a variable using exec('') since the command will be executed in different session, you have to use sp_executesql with parameter(s) to get value outside
Sorry, I didn't notice your previous comment.
I will do that.
Thanks
June 4, 2012 at 9:13 am
Can you give me an example?
June 4, 2012 at 9:16 am
check example 1 in "More Information" section
June 4, 2012 at 9:18 am
It doesn't make sense what you are doing. Why are you trying to use dynamic SQL when there is nothing dynamic there?
Just do this:
set @MaxTotalidade = select convert(bigint,isnull(max(cod_sincronismo_fim),0))
from SGCTCentral.dbo.sincronismo
where tp_processo ='I'
and cod_tp_classificacao = 2
and cod_rep_fiscal ='4.01'
SELECT @MaxTotalidade
Jared
CE - Microsoft
June 4, 2012 at 9:28 am
SQLKnowItAll (6/4/2012)
It doesn't make sense what you are doing. Why are you trying to use dynamic SQL when there is nothing dynamic there?Just do this:
set @MaxTotalidade = select convert(bigint,isnull(max(cod_sincronismo_fim),0))from SGCTCentral.dbo.sincronismo
where tp_processo ='I'
and cod_tp_classificacao = 2
and cod_rep_fiscal ='4.01'
SELECT @MaxTotalidade
You're right Jared!
June 4, 2012 at 9:52 am
I made like this:
SET @SQLString = 'select @maxVal = isnull(max(cod_sincronismo_fim),0)
from '+@DB+'.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2
and cod_rep_fiscal ='+@codrf+''
select @SQLString
SET @ParmDefinition = '@maxVal varchar(100) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@maxVal=@MaxTotalidade OUTPUT
SELECT @MaxTotalidade
But I receive this error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
June 4, 2012 at 9:56 am
river1 (6/4/2012)
I made like this:SET @SQLString = 'select @maxVal = isnull(max(cod_sincronismo_fim),0)
from '+@DB+'.dbo.sincronismo where
tp_processo =''I'' and cod_tp_classificacao =2
and cod_rep_fiscal ='+@codrf+''
select @SQLString
SET @ParmDefinition = '@maxVal varchar(100) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@maxVal=@MaxTotalidade OUTPUT
SELECT @MaxTotalidade
But I receive this error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
You really need to give us all of the code you are actually using and the DDL for the tables. Please reference the link in my signature. For example, in the above code you are not declaring any variables. I assume you are, but I don't know what data types they are or anything, so I cannot help you.
Jared
CE - Microsoft
June 5, 2012 at 2:57 am
This is the entire code:
ALTER PROCEDURE [dbo].[spSGCT_VALIDA_SGCTLocais] (@DB as varchar(50))
AS
BEGIN
set nocount on
DECLARE @MaxTotalidade as varchar(100),
@MaxDiferenca as bigint,
@Inicio as varchar(10),
@fim as varchar(10),
@CODRF as varchar(10),
@SQLString NVARCHAR(500),
@ParmDefinition NVARCHAR(500),
@IntVariable INT
DECLARE db_cursor CURSOR FOR
select distinct(codrf) from FicheirosImportar
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @codrf
delete from FicheirosImportar where sessaoinicio <> sessaofim and sessaoinicio <> 'T'
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 cod_rep_fiscal ='+@codrf+''
SET @ParmDefinition = '@maxVal varchar(100) OUTPUT'
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@maxVal=@MaxTotalidade OUTPUT
SELECT @MaxTotalidade
-- exec('set '+@MaxTotalidade+ ' =(select convert(bigint,isnull(max(cod_sincronismo_fim),0))
-- from SGCTCentral.dbo.sincronismo where
-- tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''4.01'')')
--
-- exec('set '+@MaxTotalidade+' =(select convert(bigint,isnull(max(cod_sincronismo_fim),0))
-- from SGCTCentral.dbo.sincronismo where
-- tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal =''4.01'')')
---- exec('set '+@MaxTotalidade+' = (select convert(bigint,isnull(max(cod_sincronismo_fim),0)) from '+@DB+'.dbo.sincronismo where
---- tp_processo =''I'' and cod_tp_classificacao =2 and cod_rep_fiscal ='''+@codrf+'''')
--
-- exec('set '+@MaxDiferenca+' = (select isnull(max(cod_sincronismo_fim),0) from ' +@DB+'.dbo.sincronismo where
-- tp_processo =''I'' and cod_tp_classificacao =1 and cod_rep_fiscal ='''+@codrf+'''')
-- 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
--
--
-- if ('select count(sessaofim) from FicheirosImportar where
-- sessaoinicio <> ''T'' and sessaofim > @MaxTotalidade and codrf ='''+@codrf+'''
-- and (sessaofim > '+@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 > '+@MaxTotalidade+'))')<> 0
--
--
-- BEGIN
--
--
-- exec('update FicheirosImportar set valido =1 where
-- sessaoinicio <> ''T'' and sessaofim > '+@MaxTotalidade+' and codrf ='''+@codrf+'''
-- and (sessaofim > '+@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 > '+@MaxTotalidade+'))')
--
--
--
--
-- 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
END
When I execute it like :
exec spSGCT_VALIDA_SGCTLocais 'SGCTCentral'
I Get the following message:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply