使用Open Query传递日期参数时遇未闭合引号错误求排查

Hey Claire, let's sort out that frustrating Unclosed quotation mark error you're facing with your dynamic SQL and OPENQUERY. The root cause is misaligned quote escaping when nesting string layers—this is super common with OPENQUERY since you're wrapping SQL inside another SQL string, and it's easy to miscount the quotes needed.

Let's Break Down the Problem in Your Code #

Your original code has two key issues:

1. Mismatched quote count at the end: The inner SELECT statement inside OPENQUERY wasn't properly closed with a single quote, leaving the string unbalanced.
2. Incorrect quote usage for linked server name: You used double quotes ("CWSLIVE") for the linked server, but OPENQUERY expects a single-quoted string here (we need to escape it as ''CWSLIVE'' in dynamic SQL).

Corrected Code #

Here's the fixed version with proper quote escaping and syntax alignment:

--@PID varchar(11), @START datetime, @END datetime AS 
BEGIN 
    SET NOCOUNT ON; 
    --DECLARE @PID1 varchar(11) = @PID 
    DECLARE @START1 datetime = @START 
    DECLARE @END1 datetime = @END 
    DECLARE @TSQL varchar(8000) 
    SET @TSQL = 'SELECT * FROM OPENQUERY (''CWSLIVE'', ''SELECT * FROM pricing_base_data WHERE date >= convert(date, ''''' + CONVERT(varchar, @START1, 23) + ''''', 23) AND date < convert(date, ''''' + CONVERT(varchar, @END1, 23) + ''''', 23)'')'
    --PRINT (@TSQL)  -- Uncomment this first to verify the generated SQL is valid!
    EXEC (@TSQL) 
END 
GO

Key Fixes Explained #

• Linked Server Quote Fix: We changed "CWSLIVE" to ''CWSLIVE''—in dynamic SQL, a single quote is escaped by doubling it, so this resolves to 'CWSLIVE' when the SQL is executed.
• Properly Closed Inner SQL: Added a missing single quote at the end of the inner SELECT statement (...23)') to close the string passed to OPENQUERY.
• Consistent Quote Escaping: For the date conversion, we use ''''' (five single quotes) at the start/end of the date variable. Here's why:
  • The outermost dynamic SQL uses single quotes to wrap the entire string.
  • Inside that, OPENQUERY's SQL string uses escaped single quotes ('').
  • Inside that, the date string needs another layer of escaping, so '''' becomes '' in the OPENQUERY SQL, which finally resolves to a single quote around the date value when executed.

Pro Tip for Debugging #

Always uncomment the PRINT (@TSQL) line first! This will show you the exact SQL that gets executed, so you can visually check if all quotes are balanced and the date parameters are correctly inserted. It's the fastest way to catch quote-related issues in dynamic SQL.

内容的提问来源于stack exchange，提问作者Claire