Dynamic SQL help?

Copper Contributor

Perhaps someone can help with what I'm trying to achieve. I'm lost at number of ticks and where.

I'm trying to make this execute with sp_executesql. 

select 'alter database ['+ name + '] SET ONLINE;' + char(10) + 'GO' + char(10)
from master.sys.databases
where [name] not in ('master','tempdb','model','msdb', 'blah')
and [state_desc] = 'OFFLINE'

 

So, I figured

DECLARE @sqlCommand varchar(1000)
SET @sqlCommand = 'select ''alter database [''+ name + ''] SET ONLINE;'' + char(10) + ''GO'' + char(10)
from master.sys.databases
where name not in (''master'',''tempdb'',''model'',''msdb'', ''blah'')
and [state_desc] = ''OFFLINE'''
print @sqlCommand 
--exec sp_executesql @sqlCommand

 

The print @sqlCommand didn't quite print what I needed. Not even sure if that's possible.

Thoughts? Comments? 

 

Thank you

1 Reply

@Noob2022 


The print @sqlCommand didn't quite print what I needed

Sure it don't, you don't have dynamic SQL but the query of y fix string. You see it by the sytnax highligthning in SSMS; everthing in red = one string.

If you are not familar with dyn SQL you should avoid it, see

The Curse and Blessings of Dynamic SQL (sommarskog.se)

 

This should work for you (remove the comment)

DECLARE @sqlCommand nvarchar(1000) = N'';

SELECT @sqlCommand = @sqlCommand +
       N'ALTER DATABASE ' + QUOTENAME(db.name)  + N' SET ONLINE;' + char(10) + N'GO' + nchar(10)
from master.sys.databases AS db
where name not in (N'master', N'tempdb', N'model', N'msdb', N'blah')
      -- and db.state_desc = 'OFFLINE';

print @sqlCommand