Forum Discussion

Noob2022's avatar
Noob2022
Copper Contributor
Nov 08, 2023

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

  • olafhelper's avatar
    olafhelper
    Bronze Contributor

    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 

     

     

Resources