New generic procedures - Dynamic WebMethod

Release v2.3.5: New generic procedures - Dynamic WebMethod

The web method is no longer hard coded for each procedure, meaning that with only one procedure you would be able to call whatever web method you need. For instance; POST, GET, PATCH, PUT...

Below the two new procedure;

APICaller_Web_Extended(SqlString httpMethod, SqlString URL, SqlString Headers, SqlString JsonBody)

CREATE PROCEDURE [dbo].[APICaller_Web_Extended]
@httpMethod NVARCHAR (MAX) NULL, @URL NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_Web_Extended]

APICaller_WebMethod(SqlString httpMethod, SqlString URL, SqlString JsonBody)

CREATE PROCEDURE [dbo].[APICaller_WebMethod]
@httpMethod NVARCHAR (MAX) NULL, @URL NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_WebMethod]

The only different between those two is the result returned. Extented version return more detail related to the request.

Sample script calling POST Method with procedure APICaller_Web_Extended;

GO
DECLARE @httpMethod nvarchar(max)	 = 'POST'
DECLARE @URL nvarchar(max)			 = 'https://url-shortener-service.p.rapidapi.com/shorten'
DECLARE @Headers nvarchar(max)		 =  '[{ "Name": "Content-Type", "Value" :"application/x-www-form-urlencoded" }
										 ,{ "Name": "X-RapidAPI-Host","Value" :"url-shortener-service.p.rapidapi.com"}
										 ,{ "Name": "X-RapidAPI-Key", "Value" :"c56b333d25mshdbfec15f02f096ep19fa94jsne5189032cf7d"}
										 ,{"Name": "useQueryString","Value" :"true"}]';

DECLARE @JsonBody nvarchar(max)		 =  'url=https://www.linkedin.com/in/geraldo-diaz/'

Declare @ts as table
(
	Json_Result  NVARCHAR(MAX),
	ContentType  VARCHAR(100),
	ServerName   VARCHAR(100),
	Statuscode   VARCHAR(100),
	Descripcion  VARCHAR(100),
	Json_Headers NVARCHAR(MAX)
)

DECLARE @i AS INT 
 
INSERT INTO @ts
EXECUTE @i =  [dbo].[APICaller_Web_Extended] 
			   @httpMethod
			  ,@URL
			  ,@Headers
			  ,@JsonBody

 SELECT * FROM @ts

SELECT 
		Result = [name]	
 FROM (
			SELECT Context = Json_Result 
			  from @ts
		)tb
	OUTER APPLY OPENJSON  (context)  
  WITH
    ( [name]		VARCHAR(20) '$.result_url' );

SELECT  * 
 FROM OPENJSON((select Json_Headers from @ts))  
			WITH (   
					 Header		NVARCHAR(MAX) '$."Name"'      
					,Value		NVARCHAR(MAX) '$."Value"'      
					) a

Sample script calling GET Method with procedure APICaller_Web_Extended;

GO
DECLARE @httpMethod nvarchar(max)	 = 'GET'
DECLARE @URL nvarchar(max)			 = 'https://www.routingnumbers.info/api/name.json?rn=122242597'
DECLARE @Headers nvarchar(max)		 = '[{"Name": "Content-Type", "Value" :"text/javascript; charset=utf-8" }]';

DECLARE @JsonBody nvarchar(max)		 =  ''

Declare @ts as table
(
	Json_Result nvarchar(max),
	ContentType varchar(100),
	ServerName varchar(100),
	Statuscode varchar(100),
	Descripcion varchar(100),
	Json_Headers nvarchar(max)
)

DECLARE @i AS INT 
 
INSERT INTO @ts

EXECUTE @i =  [dbo].[APICaller_Web_Extended] 
			   @httpMethod
			  ,@URL
			  ,@Headers
			  ,@JsonBody

SELECT * FROM @ts

SELECT 
		[name]	
		,[rn]		
		,[message]	
		,[code]	
 FROM (
			SELECT Context = Json_Result 
			  from @ts
		)tb
	OUTER APPLY OPENJSON  (context)  
  WITH
    ( [name]		VARCHAR(20) '$.name'
	, [rn]			VARCHAR(20) '$.rn'
	, [message]		VARCHAR(20) '$.message'
	, [code]		INT			'$.code'
    );

SELECT  * 
 FROM OPENJSON((select Json_Headers from @ts))  
			WITH (   
					 Header		NVARCHAR(MAX) '$."Name"'      
					,Value		NVARCHAR(MAX) '$."Value"'      
					) a

Bug fixes:

In this release were fixed the issues below;

#36 - Patch Method
#42 - Oauth 2 with APICaller_POST_Encoded
#44 - ERROR: Procedure APICaller_POST_Extended - Column name or number of supplied values does not match table definition