VBA writing SQL String using ' without it getting commented out?

Question

I'm trying to figure out how to write this line in VBA so it doesn't comment out but comes up in my SQL query correct.

The line issue is this:

 & "SELECT @cols = @cols + stuff((select '," ' + offer + '"' " _

after the the second ' is when it disables the rest of the code. Here is an example of the Code with more detail.

         StrSQL = "SET NoCount ON " _
        & "DECLARE @cols  AS NVARCHAR(MAX)=''; " _
        & "DECLARE @query AS NVARCHAR(MAX)=''; " _
        & "SELECT @cols = @cols + stuff((select '," ' + offer + '"' " _  
        & "FROM catcov y join CatalogInfo z on (y.MailYear = z.mailyear) and (y.offer = z.catalog) " _

Any help would be appreciated.

EDIT: Solution to this issue was to add an extra " to my line so it looks like this.

& "SELECT @cols = @cols + stuff((select ',"" ' + offer + '""' " _

Thanks for the help all!

Single quotes inside double quotes aren't treated as comments... — Thom A, Jun 11 '20 at 20:00
If you're trying to create a delimited list in SQL Server, don't use recursive variable reference. Use `STRING_AGG` or the method shown in [this answer](https://stackoverflow.com/a/18870585/2029983). Then you don't need the `NVARCHAR(MAX)=''`. Considering this looks to be the start of a dynamic query too, I suggest quoting your dynamic columns with `QUOTENAME` too. — Thom A, Jun 11 '20 at 20:05
@Larnu I'm not familiar with STRING_AGG I'm still learning SQL, self taught. You are correct though this is for dynamic columns. I thought by adding the ` + offer + '"' I was adding the quotes to the column names. Adding it resolves an issue I was having running it in SQL server but once I added it to VBA (to be written as a string in VBA) it disables everything after the first ' so I'm not sure who to write so it doesn't disable my code. I'm looking through the resource you gave me though. Hopefully I can figure it out. Thanks!!! — Deke, Jun 11 '20 at 20:43
If you actually want to have double quotes in your string, you must duplicate them. `str = "This is a string with ""double quotes""."` — Andre, Jun 11 '20 at 22:47

score 0 · Answer 1 · answered Jun 12 '20 at 09:29

0

As I mentioned in the comments, don't use a self referencing variable here. If you're on a modern version of SQL Server, then you can use STRING_AGG to easily achieve what you're after:

     StrSQL = "SET NoCount ON " _
    & "DECLARE @cols  AS NVARCHAR(MAX); " _
    & "DECLARE @query AS NVARCHAR(MAX); " _
    & "SET @cols = (SELECT STRING_AGG(offer,',') WITHIN GROUP (ORDER BY offer)" _
    & "             FROM catcov cc JOIN CatalogInfo CI ON (cc.MailYear = CI.mailyear) AND (cc.offer = ci.catalog));" _ ' does this actually continue afterwards?

If, however, you're on an older version, and don't have access to STING_AGG you can use the FOR XML PATH method:

     StrSQL = "SET NoCount ON " _
    & "DECLARE @cols  AS NVARCHAR(MAX); " _
    & "DECLARE @query AS NVARCHAR(MAX); " _
    & "SET @cols = (SELECT CONCAT(',',offer)" _
    & "             FROM catcov cc JOIN CatalogInfo CI ON (cc.MailYear = CI.mailyear) AND (cc.offer = ci.catalog)" _
    & "             FOR XML PATH""),TYPE).value('.','nvarchar(MAX)');" _ ' does this actually continue afterwards?

Unfortunately, I can't test either of these, but if there are any typoes, hopefully you can fix them.

answered Jun 12 '20 at 09:29

Thom A

88,727
11
45
75

I'm using Microsoft SQL Server v. 17.9.1 for SQL. STRING_AGG doesn't come back as a recognized function. I do have more code that goes with this if you wanted to see the whole thing I can add it if that will help. I'm testing your suggestions with the XML path method which is kind of how I have this working. I am using a dynamic pivot here so there is a lot going on in this code. – Deke Jun 13 '20 at 01:45
The latest version of SQL Server is version 15, which is 2019, @Deke . There is no version 17.9.1 (and I doubt there ever will be, as they rarely get past V.0 let along to V.9). – Thom A Jun 13 '20 at 08:04
I've added an image of the version data from my SQL. It shows 17.9.1 I don't know if it will help. – Deke Jun 13 '20 at 13:44
That's your version of SQL Server **Management Studio** @Deke ... The version of SSMS you have has very little to do with the version of SQL Server you have (It's like saying you have Office 2013 installed when asked what operating system you have). The version of SQL Server you have is shown when you connect to the server you are using, or you can use [`@@VERSION`](https://learn.microsoft.com/en-us/sql/t-sql/functions/version-transact-sql-configuration-functions?view=sql-server-ver15) once connected. – Thom A Jun 13 '20 at 18:02
Yeah sorry I'm no expert. I'm self taught and new to SQL. I did figure out my issue it was simple and stupid I just needed to add an extra " to my statement. – Deke Jun 14 '20 at 16:05

VBA writing SQL String using ' without it getting commented out?

1 Answers1