0

I have a issue with a query in SQL SERVER, I have wrote this Dynamic Query:

declare @cont int
declare @sqlquery varchar(1000)
declare @sqlquery2 varchar(500)
declare @sqlquery3 varchar(2000)
declare @anho varchar(4)

set nocount on

drop table ti

set @anho = (select year(getdate()))
set @cont = 1
set @sqlquery = ''

while @cont <= 12
begin

set @sqlquery2 = '(a.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+b.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+c.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+d.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']) as ['+@anho + RIGHT('00'+cast(@cont as varchar),2)+'],'
exec (@sqlquery2)

set @sqlquery = @sqlquery + @sqlquery2
exec (@sqlquery)

set @Cont = @Cont + 1
end

exec(@sqlquery)

set @sqlquery3 = 'select a.gestion,'+@sqlquery+' '+quotename('GES08','''')+' as COD_GES into ti from Llamadas_Mensual_Oro_Final a inner join Llamadas_Mensual_plata_Final b on a.gestion = b.gestion inner join Llamadas_Mensual_Reten_Final c on b.gestion = c.gestion inner join Llamadas_Mensual_cable_Final d on c.gestion = d.gestion'

exec(@sqlquery3)

set nocount off

select * from ti

Well, the issue that i have is when I execute the query with a Store Procedure, It's Work's well, but when I execute only the code, SQL SERVER show me a error message like this:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'a'.

The query is work's ok, but i want to hide the error message, can anybody help me to this issue?

Improve this question
2
  • Here is the error: exec (@sqlquery2) because, @sqlquery2 has (a.[.... You're trying to assign the value of the query into an variable with EXEC, you should do this another way. Commented Jul 23, 2013 at 18:05
  • Can you print @sqlquery and @sqlquery2 before executing them and post the query here? Commented Jul 23, 2013 at 18:05

3 Answers 3

Reset to default
1

I have no idea why it would work in a stored procedure.

Your first expression:

exec (@sqlquery2)

Is being run on the following strings:

(a.[201301]+b.[201301]+c.[201301]+d.[201301]) as [201301],
(a.[201302]+b.[201302]+c.[201302]+d.[201302]) as [201302],
(a.[201303]+b.[201303]+c.[201303]+d.[201303]) as [201303],
(a.[201304]+b.[201304]+c.[201304]+d.[201304]) as [201304],
(a.[201305]+b.[201305]+c.[201305]+d.[201305]) as [201305],
(a.[201306]+b.[201306]+c.[201306]+d.[201306]) as [201306],
(a.[201307]+b.[201307]+c.[201307]+d.[201307]) as [201307],
(a.[201308]+b.[201308]+c.[201308]+d.[201308]) as [201308],
(a.[201309]+b.[201309]+c.[201309]+d.[201309]) as [201309],
(a.[201310]+b.[201310]+c.[201310]+d.[201310]) as [201310],
(a.[201311]+b.[201311]+c.[201311]+d.[201311]) as [201311],
(a.[201312]+b.[201312]+c.[201312]+d.[201312]) as [201312],

None of these are valid SQL statements. You can see, the failure is on a not being recognized.

My best guess is that the code in the stored procedure is not really the code you are showing in the question. Possibly something is being left out.

EDIT:

You want code something like this:

declare @cont int;
declare @sqlquery varchar(1000);
declare @sqlquery2 varchar(500);
declare @sqlquery3 varchar(2000);
declare @anho varchar(4);

set nocount on

drop table ti

set @anho = (select year(getdate()))
set @cont = 1
set @sqlquery = 'select a.gestion, '

set @sqlquery = 

while @cont <= 12
begin

set @sqlquery2 = '(a.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+b.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+c.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+d.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']) as ['+@anho + RIGHT('00'+cast(@cont as varchar),2)+'], '

--exec (@sqlquery2)
set @sqlquery = @sqlquery + @sqlquery2

set @Cont = @Cont + 1
end

set @sqlquery = @sqlquery + ' 'GES08' as COD_GES from Llamadas_Mensual_Oro_Final a inner join Llamadas_Mensual_plata_Final b on a.gestion = b.gestion inner join Llamadas_Mensual_Reten_Final c on b.gestion = c.gestion inner join Llamadas_Mensual_cable_Final d on c.gestion = d.gestion'
print @sqlquery;
exec(@sqlquery);

In particular, you need to understand the difference between executing a string and just printing it out.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Gonzalo . . . The answer is to construct the SQL query that you really want. Given the snippet of code that doesn't work, I can't really give more guidance than that.
...I've trying to build a SQL Query like this: select a.gestion, (a.[201301]+b.[201301]+c.[201301]+d.[201301]) as [201301], (a.[201302]+b.[201302]+c.[201302]+d.[201302]) as [201302], (a.[201303]+b.[201303]+c.[201303]+d.[201303]) as [201303],'GES08' as COD_GES from Llamadas_Mensual_Oro_Final a inner join Llamadas_Mensual_plata_Final b on a.gestion = b.gestion inner join Llamadas_Mensual_Reten_Final c on b.gestion = c.gestion inner join Llamadas_Mensual_cable_Final d on c.gestion = d.gestion
0

it looks like "sqlquery2" is missing the "select"

instead of set @sqlquery = '', try this:

set @sqlquery = 'select ' -- then you build the query and append sqlquery2, it might make sense.

Improve this answer

Comments

0

This part is COMPLETELY wrong:

set @sqlquery2 = '(a.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+b.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+c.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']+d.['+@anho + RIGHT('00'+cast(@cont as varchar),2)+']) as ['+@anho + RIGHT('00'+cast(@cont as varchar),2)+'],'
exec (@sqlquery2)

There's no SELECT or FROM in this statement, it will never execute. EVER. You need to construct an actual SQL statement that will execute properly before this Dynamic Query will work.

Improve this answer

1 Comment

Well, now I solve the problem, I just delete the syntax exec (@sqlquery2) and exec (@sqlquery) and only use exec (@sqlquery3), and It's running ok.....thanks for all...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.