Post reply

Double quotes inside double quotes

  • August 11, 2005 at 10:25 pm

    #88607

    Hi all,

    I encountered a pretty complicated dynamic SQL as below:

    set @SQL = ' select * from openquery(MyLinkedServer, "WITH MEMBER [measures].[COST] as '' -- 2 single quotes

    iif( val [Customer].CurrentMember.Properties(credited) ) = -1, [measures].[net],[measures].[gross] ) '' -- 2 single quotes

    select {[Customer].[Cust_name].members} on columns,

    {[measures].[COST]} on rows from [MyCube]

    where [Product].[Product_ID].[' + @Prod_ID + '] ") '

    exec(@SQL)

    The above query looks OK. Only the quotes before WITH MEMBER and the last quote before closing bracket, are double quotes.

    However, the correct usage of the above query is that I need to add a pair of double quotes for the property name credited, making it [Customer].CurrentMember.Properties("credited)" ) = -1

    But as I am already using double quotes for the argument in openquery, and single quotes for the dynamic SQL, I can't figure out any method to add this pair of double quotes into the dynamic SQL.

    We can use 2 single quotes inside a quoted string to denote a single quote inside the string, but what about double quotes?

    Can anyone help me?

    Lots of thanks,

    delpiero

  • August 12, 2005 at 5:15 am

    #581703

    If you are already building dynamic SQL why not add

    + CHAR(34) + around credited ?  May take some work but this should help...



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • August 12, 2005 at 5:46 am

    #581710

    Hi Delpiero,

    Are you sure your errors aren't coming from not cast / converting @Prod_ID? You shouldn't have too many problems adding double quotes into a string.....

    So this:-

    set @SQL = 'Select * from openquery(MyLinkedServer, "WITH MEMBER [measures].[COST] as '' -- 2 single quotes

    iif( val [Customer].CurrentMember.Properties("credited") ) = -1, [measures].[net],[measures].[gross] ) '' -- 2 single quotes

    select {[Customer].[Cust_name].members} on columns,

    {[measures].[COST]} on rows from [MyCube]

    where [Product].[Product_ID].[' + CAST(@Prod_ID AS Varchar(50)) + '] ")'

    Doesn't work?

    Have fun

    Steve

    We need men who can dream of things that never were.

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

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