Forum Discussion
Noob2022
Nov 08, 2023Copper Contributor
Dynamic SQL help?
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
- olafhelperBronze Contributor
The print @sqlCommand didn't quite print what I neededSure 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