Script for Linked Server to query REST API using ODBC driver created using Progress DataDirect OpenAccess SDK

sp_stocks.sql

USE [Chinook2]
GO

/****** Object:  StoredProcedure [dbo].[sp_stocksapi]    Script Date: 3/29/2018 10:37:48 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_stocksapi]
(
	@pfunction varchar(100), 
	@psymbol varchar(5), 
	@pinterval varchar(10))

AS
BEGIN
	DECLARE @TSQL varchar(8000)
	IF @pinterval is null
	BEGIN
		SELECT @TSQL='SELECT * FROM OPENQUERY(OPENACESSSTOCKS,''select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES WHERE "function" = ''''' + @pfunction + ''''' and symbol = '''''+ @psymbol + ''''''')'
	END

	IF @pinterval is not null
	BEGIN
		SELECT @TSQL='SELECT * FROM OPENQUERY(OPENACESSSTOCKS,''select INFORMATION,"FUNCTION",SYMBOL,LASTREFRESHED,"INTERVAL",OUTPUTSIZE,TIMEZONE,TIMESTAMP_RECORDED,OPEN,HIGH,LOW,CLOSE,CAST(VOLUME as decimal(38,6)) as volume FROM TIMESERIES WHERE "function" = ''''' + @pfunction + ''''' and symbol = '''''+ @psymbol + ''''' and "interval"=''''' +@pinterval+ ''''''')'
	END


	EXEC(@TSQL);

END
GO