Double quotes inside double quotes
-
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
-
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 -
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