Dynamic Query issue

Question

Dynamic Query issue

sqlnaive

SSCoach

Points: 17437
More actions
January 14, 2014 at 6:39 am

#287217

I am trying to write a dynamic query within a while loop and executing it like this
create table #temp(Col1 varchar(20))
declare @p nvarchar(max), tabname varchar(255)
while @a <= @b-2
begin
select @p = 'select col1 from '+@tabname -- this keep changing with each loop
insert into #temp
exec (@p)-- stat populate
@a = @a+1
end
The thing is that everytime it breaks after going into (stat populate). Shoud it not be done this way ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please will you post the actual code you're running? If that is it, I would recommend sorting out the typos, syntax errors and undeclared variables before you worry about why it's breaking.

John.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

sqlnaive (1/14/2014)
I am trying to write a dynamic query within a while loop and executing it like this
create table #temp(Col1 varchar(20))
declare @p nvarchar(max), tabname varchar(255)
while @a <= @b-2
begin
select @p = 'select col1 from '+@tabname -- this keep changing with each loop
insert into #temp
exec (@p)-- stat populate
@a = @a+1
end
The thing is that everytime it breaks after going into (stat populate). Shoud it not be done this way ?

You are missing the SET keyword on the line where you increment @a.

I hope this isn't your real code. It would be a million times easier to read code when the variable names have some sort of meaning instead of just single characters.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlnaive SSCoach Points: 17437 More actions · Answer 3

Sorry I wasn't clear. Here is the code:

while @a <= @b-2

begin

---

select @p = 'select col1 from '+@tabname -- this keep changing with each loop

insert into #temp

exec (@p)-- stat populate

---

end

So the thing is i'm taking care of everything and there is no issue at all. Just that the following code is not working:

insert into #temp

exec (@p)

My question is whether should it work like this or not ?

Stuart Davies SSCoach Points: 19505 More actions · Answer 4

sqlnaive (1/14/2014)
Sorry I wasn't clear. Here is the code:
while @a <= @b-2
begin
---
---
select @p = 'select col1 from '+@tabname -- this keep changing with each loop
insert into #temp
exec (@p)-- stat populate
---
---
---
end
So the thing is i'm taking care of everything and there is no issue at all. Just that the following code is not working:
insert into #temp
exec (@p)
My question is whether should it work like this or not ?

Not sure with out seeing the actual code you are running (along with some sample data if possible).

But something tells me that the the #temp table won't be visible to the dynamic sql.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlnaive (1/14/2014)
My question is whether should it work like this or not ?

Should work. Try printing out the generated dynamic SQL and looking at the one that breaks and seeing what it's trying to do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abu Dina SSChampion Points: 14155 More actions · Answer 6

So what is @tabname? The variable is declared in your code but is not being assigned anything?

With dynamic SQL it's a good idea to PRINT the content first to make sure it's what you want then running it.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Stuart Davies (1/15/2014)
But something tells me that the the #temp table won't be visible to the dynamic sql.

It will be, but since it's not referenced anywhere inside the dynamic SQL it makes no difference

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 8

Strangely it started working but still worst part is that I don't know what made it work. I tried lots of things but couldn't track what worked. I am sure there was no code issue anywhere but still something was missing while implementation because it never thrown any errors.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

When you're debugging or performance tuning, try one thing at a time, test, evaluate, try something else. Change a bunch of stuff and you'll never be able to figure out which of the bunch of changes actually did something.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 10

Yeah, agreed Gail. After lots of efforts, i started getting frustate and tried too much in too little time. Glad that it worked and sad that couldn't found the solution. Will keep more patience next time.