1

Could someone look at my code below and let me know where I am going wrong. Trying to pass date parameters through Open Query & getting error - Unclosed quotation mark after the character string 

--@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)
EXEC (@TSQL)
END

GO
Claire
  • 51
  • 6

1 Answers1

2

Use your query like this:- double quotes both side: "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))"

Vijay Kumar Mahar
  • 59
  • 4
  • I'm getting the following error message:- Msg 102, Level 15, State 1, Procedure SP_CWS_PriceSearchTesting, Line 20 Incorrect syntax near 'CWSLIVE'. Msg 103, Level 15, State 4, Procedure SP_CWS_PriceSearchTesting, Line 20 The identifier that starts with ', ''SELECT * FROM pricing_base_data WHERE date >= convert(date, ''''' + convert(varchar, @START1, 23)+ ''''', 23) and date <' is too long. Maximum length is 128. – Claire May 09 '18 at 09:59
  • sub-query that you using in wrong way Please run first "select * from `your_table_name` where your_column `your condition like equal greater then` (select * from `your_table_name` where your_column ) " – Vijay Kumar Mahar May 09 '18 at 10:06
  • Sorry, I don't understand. What does my final code need to look like? – Claire May 09 '18 at 10:20