Embadded Select in another Select and using apostrophes / quotes

Question

Post reply

Embadded Select in another Select and using apostrophes / quotes

Spar

Valued Member

Points: 62
More actions
December 19, 2022 at 10:00 pm

#4127688
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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

You code appears to be a part of Dynamic SQL. If so, then replace each single quote with two single quotes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

code is a snippet - missing parts which on their own can be wrong.

it is creating a proc with prefix SP_ - this should be avoided.

and its dynamic sql inside dynamic sql - so every single quote that would exist on the base sql would need to be escaped - and then each one of them escaped again.

so my advise (other than do not do it this way !!) is to break the code on its individual components starting with the lowest level (inner) one - once you have that one right and are ready to put it onto the dynamic sql block, escape all single quotes.

once that next level is ready, escape all single quotes again and put it on the outer @sql

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

The way I write such code is to ...

Write it like normal, properly indented code.
Test the dickens out of it to make sure it works.
Doe a search and replace to convert all single quotes to double quotes.
Wrap the whole thing in single quotes.
When I assign it to a variable for execution, wrap the the thing with a REPLACE to replace the double quotes with single quotes.

It makes things real easy to modify in the future. Just reverse the simple double quote process, remove the start and end single quote, and you're back to working code to modify.

There's more that can be done in that area but you DO have to take precautions about SQL Injection, which is still one of the leading causes for "Penetrations".

I also tend to use sp_ExecuteSQL instead of just EXEC(@SQL) or whatever your variable name is.

And totally agreed on NOT using Hungarian Notation such as "sp_". That one, especially, has some underlying issues with performance and a couple of other things that catch people unaware. The only time I use it is when I actually want to create a "system SPecial procedure" in the Master database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)