Embadded Select in another Select and using apostrophes / quotes

Transact-SQL
1

Hello,

I have problem with below script. I'm trying to run below script on few servers in one go. I would like to firstly check if this procedure already exist, if yes - do nothing, if no - create.


IF (OBJECT_ID('[dbo].[sp_db_files_sizing_info]', 'P') IS NULL)
BEGIN
DECLARE @sql NVARCHAR(MAX)

SELECT @sql = '
                CREATE procedure [dbo].[sp_db_files_sizing_info]
AS

BEGIN
-- To capture DATA + LOG Initial size, space used & MAX sizes as well
-- for SPOC SharePoint Server, created below:
  
set nocount on 
create table #dbfileInfo
                (...)

                 WHILE @@FETCH_STATUS = 0
                      BEGIN
                      PRINT @dbName
                      if DATABASEPROPERTYEX(@dbName, ''status'') = ''ONLINE'' 
                                      
                                      BEGIN
                                      select @mySQL =     
                                      ''    
                                      use ['' + @dbName + '']   
                                      INSERT INTO #dbfileInfo        
                                      select      name    
                                      , filename    
                                      , convert(decimal(12,2),round(a.size/128.000,2)) as InitialFileSizeMB    
                                      , convert(decimal(12,2),round(fileproperty(a.name,SpaceUsed)/128.000,2)) as SpaceUsedMB    
                                      , convert(decimal(12,2),round((a.size-fileproperty(a.name,SpaceUsed))/128.000,2)) as FreeSpaceMB 
                                      , convert(decimal(12,2),round(a.maxsize/128.000,2)) as MaxSizeMB  
                                      , (convert(decimal(12,2),round(a.maxsize/128.000,2)) - convert(decimal(12,2),round(fileproperty(a.name,SpaceUsed)/128.000,2)) ) as SpaceToGrowStillMB
                                      from dbo.sysfiles a  
 ''    

exec sp_executesql @mySQL
END

Exactly I have problem with SpaceUsed value in the last part of my script. Normally, script has single quote, and it look like here:


, convert(decimal(12,2),round(a.size/128.000,2)) as InitialFileSizeMB

, convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB

, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB

, convert(decimal(12,2),round(a.maxsize/128.000,2)) as MaxSizeMB

, (convert(decimal(12,2),round(a.maxsize/128.000,2)) - convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) ) as SpaceToGrowStillMB

from dbo.sysfiles a

But when I add it under the IF section, when I have to put script into a quote SELECT @sql = ' my script ' to execute it at the end by EXEC sp_executesql @sql, I really dont know what type of quote I need to use that SpaceUsed is recognize as it should. Doesn't matter if I use ', '', ''' or no, I got: Incorrect syntax near 'SpaceUsed'.

Without any quotes at least I can create this StoredProcedure, the then it works not correctly.

Could you please help me?

Spar

2

You need additional quotes around the USE, like this:
...
use ['''' + @dbName + '''']
...