Post reply

Using single talking marks (') with Dynamic SQL?

  • October 29, 2014 at 9:52 pm

    #291622

    Hi Everyone,

    I am new to Dynamic SQL and have become quite stuck regarding how to included aliases which have spaces in my query. A related issue, which I believe also revolves around the use of single talking marks (') within Dynamic SQL is how to include argument values and variables in my queries.

    From my limited research it 'feels like' what I need to do is the opposite of ESCAPE sequencing...

    My issue is better explained through some code samples.

    Each of the samples below assume the use of a variable within the overall query, e.g.: DECLARE @sql nvarchar(max)

    Here is a sample from a working query (excuse the brevity)

    Now if I put a space into the Alias name as shown below the query will not run...

    Similarly I am wondering how to go about assigning variables, within for example a WHERE clause?

    Any help with the above broad questions will be greatly appreciated.

    Kind Regards,

    Davo

  • October 30, 2014 at 1:46 am

    #1755691

    Hi ,

    a quick way is to use char(39) which is a single quote:-

    '...ORDER BY product)) AS ' +char(39)+ 'BIN Col' + CHAR(39) . . . .

    It should work whenever you need a single quote.

    Security considerations aside, dynamic SQL can be a real pain to read and fault find.

    I only tend to use it where I have no other option.

    You second example (the where clause) should be ok as is, assuming that none of your parameters are text, then try the method above.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • October 30, 2014 at 1:50 am

    #1755695

    Quick thought, the dynamic sql should be nvarchar so use NCHAR(39) where it's needed, it is on the other hand not needed for the parameters, use sp_executesql and the execution parameters parameter

    😎

  • October 30, 2014 at 1:52 am

    #1755696

    You need to escape single quotes with another single quote.

    An example:

    SELECT 'This string will escape ''this other string''.'

    Instead of using ' for your aliases, you could also use square brackets. Less issues 😀

    SELECT 'Hello World AS [Hello World]'

    Regarding the variables: if they are text (or dates which are implicitly cast from text to dates), you will also need to escape them with single quotes.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 30, 2014 at 2:28 am

    #1755706

    There was an article about this some time ago - http://www.sqlservercentral.com/articles/T-SQL/95670/

    Steve Hall
    Linkedin
    Blog Site

  • October 30, 2014 at 10:35 pm

    #1755962

    Thanks everyone for your generous contributions. Ultimately I found three single talking marks to be most effective, '''!

    ...

    WHERE T1.DocDate >=''' + @fromDate + ''' AND T1.DocDate <=''' + @toDate + '''

    GROUP BY T0.ItemCode, T0.Dscription, T3.CardCode, T3.CardName, T5.OnOrder, T5.IsCommited, T5.OnHand, T4.BINLABEL, T4.QUANTITY

    ) AS Tx

    GROUP BY Tx.[Item Code], Tx.[Item Description], Tx.[Supplier Code], Tx.[Supplier Name], Tx.OnOrder, Tx.[Commited], Tx.OnHand

    ORDER BY Tx.[Item Code]'

Viewing 6 posts - 1 through 5 (of 5 total)

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