CODEWITHSUNDEEP
sqlsql-serverstored-proceduresopenquery
user27768
user27768

Reputation: 13

Execute a literal string as a SQL statement - placement of single quotes

I have the following SQL string pulls data from a ODBC Progress Database via a linked server. This query works fine. 

SELECT * 
INTO [TABLE] 
FROM 
OPENQUERY
(
"ODBC DRIVER",
'SELECT CAST(SCHEMA."AB_ACCESSIBILITY"."ACCESS_CODE" AS VARCHAR(20)) AS "ACCESS-CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."DESCRIPTION" AS VARCHAR(60)) AS "DESCRIPTION"
,CAST(SCHEMA."AB_ACCESSIBILITY"."ORG_CODE" AS VARCHAR(16)) AS "ORG_CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."POINTS" AS INT) AS "POINTS" 
FROM SCHEMA."AB_ACCESSIBILITY" with (nolock)'
)

However, I would like to convert this to a literal string so that I can execute this from a stored procedure using exec sp_executesql.

However, I'm having trouble with correct placement of apostrophes so that exec sp_executesql can read it as a properly formed SQL statement.

Could someone please show me where to add additional apostrophes to the above statement so that it will work with exec sp_excutesql?

Thanks!

Edits for clarity:

  1. With (nolock) is fine for the above

  2. The purpose of the above is to loop over a number of SQL statements in a list to import data from a Progress DB.

Upvotes: 1

Views: 1378

Answers (1)

Joe C
Joe C

Reputation: 3993

Looks like you just need to escape the ' characters with ''

SELECT * 
INTO [TABLE] 
FROM 
OPENQUERY
(
"ODBC DRIVER",
''SELECT CAST(SCHEMA."AB_ACCESSIBILITY"."ACCESS_CODE" AS VARCHAR(20)) AS "ACCESS-CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."DESCRIPTION" AS VARCHAR(60)) AS "DESCRIPTION"
,CAST(SCHEMA."AB_ACCESSIBILITY"."ORG_CODE" AS VARCHAR(16)) AS "ORG_CODE"
,CAST(SCHEMA."AB_ACCESSIBILITY"."POINTS" AS INT) AS "POINTS" 
FROM SCHEMA."AB_ACCESSIBILITY" with (nolock)''
)

Upvotes: 1

Related Questions