problem with select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with select statement

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-05 : 01:53:14

select @sql = 'USE '+DatabaseName+' ALTER INDEX '+IndexName+' ON '+TableName+' REBUILD with (ONLINE=ON,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=OFF);' from #minatest
exec sp_executesql @sql

this isnt even running anything??? what am i doing wrong

Kristen
Test

22859 Posts

Posted - 2007-01-05 : 01:58:45

Put a

PRINT @sql

between those two statements, and trying running what it displays to see what errors/output you get

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-05 : 02:05:37

okay theres no error message its just returning 1 sql statement wouldnt it run on all items in the minatest?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-05 : 02:12:07

How it would unless you specify to do it?

Select @sql = Coalesce(@sql, '') +  ' USE '+DatabaseName+'; ALTER INDEX '+IndexName+' ON '+TableName+' REBUILD with (ONLINE=ON,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=OFF);' from #minatest
exec sp_executesql @sql

Note: Be sure to keep @sql sufficiently large to hold all queries.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-05 : 02:13:53

i guess i wanted to try do it without a cursor but doesnt look possible

Kristen
Test

22859 Posts

Posted - 2007-01-05 : 02:15:21

"Select @sql = Coalesce(@sql, '') + ..."

Harsh: Isn't there a problem with this syntax under SQL2005? Or have I misremembered and its only to do with trying to get an ORDER BY as well or somesuch ??

Kristen

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-05 : 02:16:13

done deal. thanx mate!!!!

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-05 : 02:16:57

that worked... i like how harsh harsh is

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-05 : 02:28:52

quote:
Originally posted by Kristen

"Select @sql = Coalesce(@sql, '') + ..."

Harsh: Isn't there a problem with this syntax under SQL2005? Or have I misremembered and its only to do with trying to get an ORDER BY as well or somesuch ??

Kristen

I don't know of any problem with this...is it with the using Coalesce() or assigment operation using SELECT ?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-05 : 08:28:22

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-01-05 : 10:17:59

"is it with the using Coalesce() or assigment operation using SELECT ?"

What I remember was that doing

COALESCE(MyCol, '') + ...

could only return the last item, not all the items concatenated - but I can't remember the circumstances

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-05 : 10:38:55

No. That is not the case. See this:

declare @r varchar(8000)

select @r = coalesce(@r + ',', '') + au_lname from pubs.dbo.authors 
order by au_lname

select @r

This gives proper comma-delimited list. But I have seen the problem you described, unfortunately I don't remember the scenario which caused it. The only way I was able to replicate it was by adding,

ORDER BY 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources