SQL Server
SQL Server
curl How Known Variables Reduce Execution Steps
See more CURL Examples
This example demonstrates how previously resolved values can simplify future executions.
When DoYourThing is called for a curl command, it builds an execution plan by resolving any missing inputs. This may require running one or more helper curl commands to produce the needed values.
As each command runs, any defined output variables are stored in the HttpCurl object and remain available for subsequent calls.
If another curl command is executed afterward—and it requires variables that are already known from earlier executions—those inputs do not need to be resolved again. As a result, the dependency chain is shorter, and the execution plan contains fewer steps.
In other words, each call to DoYourThing benefits from the current set of known variables. The more values that have already been resolved, the simpler and more direct the execution plan becomes.
This demonstrates that dependency resolution is dynamic and incremental: only missing inputs trigger additional steps, while previously computed values are reused automatically.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
SELECT @success = 0
-- -----------------------------------------------------------------------------
-- NOTE:
-- This example builds on the earlier multi-step dependency example, where the
-- execution plan required multiple curl commands (getSite → getDrives → target).
--
-- If you have not seen that example, refer to it here: curl Multi-Step Dependency
--
--
-- In this example, we demonstrate how previously resolved variables (site_id,
-- drive_id, etc.) are reused across multiple calls to DoYourThing, resulting
-- in simpler execution plans for subsequent requests.
-- -----------------------------------------------------------------------------
DECLARE @httpCurl int
EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @httpCurl OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- The final curl command we want to execute.
-- It depends on {{drive_id}}, which may or may not already be known.
DECLARE @targetCurl nvarchar(4000)
SELECT @targetCurl = 'curl -X GET https://graph.microsoft.com/v1.0/drives/{{drive_id}}/root/children'
-- Define helper function to get drives (produces drive_id, requires site_id)
DECLARE @fnName nvarchar(4000)
SELECT @fnName = 'getDrives'
EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://graph.microsoft.com/v1.0/sites/{{site_id}}/drives'
DECLARE @jsonPath nvarchar(4000)
SELECT @jsonPath = 'value[0].id'
EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'drive_id'
-- Define helper function to get site (produces site_id, requires site_name)
SELECT @fnName = 'getSite'
EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'GET https://graph.microsoft.com/v1.0/sites/root:/sites/{{site_name}}'
SELECT @jsonPath = 'id'
EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, @jsonPath, 'site_id'
-- Provide the initial known input.
EXEC sp_OAMethod @httpCurl, 'SetVar', NULL, 'site_name', 'test'
-- Configure OAuth2 authentication (client credentials with secrets)
DECLARE @jsonOAuth2 int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOAuth2 OUT
EXEC sp_OASetProperty @jsonOAuth2, 'EnableSecrets', 1
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.client_id', '!!sharepoint|oauth2|client_id'
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.client_secret', '!!sharepoint|oauth2|client_secret'
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.scope', 'https://graph.microsoft.com/.default'
EXEC sp_OAMethod @jsonOAuth2, 'UpdateString', @success OUT, 'oauth2.token_endpoint', '!!sharepoint|oauth2|token_endpoint'
EXEC sp_OAMethod @httpCurl, 'SetAuth', @success OUT, @jsonOAuth2
-- -----------------------------------------------------------------------------
-- EXAMINE THE EXECUTION PLAN (BEFORE RUNNING):
-- At this point, drive_id and site_id are NOT known.
-- We can inspect the plan that will be executed without sending any requests.
-- -----------------------------------------------------------------------------
DECLARE @planJson int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @planJson OUT
EXEC sp_OASetProperty @planJson, 'EmitCompact', 0
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
PRINT 'Execution plan before first call:'
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected output:
--
-- {
-- "plan": [{
-- "function": "getSite",
-- "inputs": ["site_name"],
-- "outputs": ["site_id"]
-- },{
-- "function": "getDrives",
-- "inputs": ["site_id"],
-- "outputs": ["drive_id"]
-- },{
-- "function": "targetCurl",
-- "inputs": ["drive_id"],
-- "outputs": []
-- }]
-- }
-- -----------------------------------------------------------------------------
-- FIRST CALL:
-- At this point, drive_id and site_id are NOT known.
-- The execution plan will include all required steps:
--
-- 1) getSite → produces site_id
-- 2) getDrives → produces drive_id
-- 3) targetCurl → uses drive_id
-- -----------------------------------------------------------------------------
EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @targetCurl
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
EXEC @hr = sp_OADestroy @planJson
RETURN
END
PRINT 'First call completed.'
EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'site_id'
PRINT 'site_id = ' + @sTmp0
EXEC sp_OAMethod @httpCurl, 'GetVar', @sTmp0 OUT, 'drive_id'
PRINT 'drive_id = ' + @sTmp0
-- -----------------------------------------------------------------------------
-- SECOND CALL (DIFFERENT TARGET):
-- Now that site_id is already known, we can run another curl command that
-- depends only on site_id. No need to call getSite again.
--
-- Execution plan:
--
-- 1) targetCurl2 → uses existing site_id
-- -----------------------------------------------------------------------------
DECLARE @targetCurl2 nvarchar(4000)
SELECT @targetCurl2 = 'curl -X GET https://graph.microsoft.com/v1.0/sites/{{site_id}}'
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl2, @planJson
PRINT ''
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected output:
--
-- {
-- "plan": [{
-- "function": "targetCurl",
-- "inputs": ["site_id"],
-- "outputs": []
-- }]
-- }
EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @targetCurl
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
EXEC @hr = sp_OADestroy @planJson
RETURN
END
PRINT 'Second call completed (used existing site_id).'
-- -----------------------------------------------------------------------------
-- THIRD CALL (ORIGINAL TARGET AGAIN):
-- drive_id is already known from the first call.
-- Therefore, no helper functions are needed this time.
--
-- Execution plan:
--
-- 1) targetCurl → uses existing drive_id
-- -----------------------------------------------------------------------------
EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson
PRINT ''
EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Expected output:
--
-- {
-- "plan": [{
-- "function": "targetCurl",
-- "inputs": ["drive_id"],
-- "outputs": []
-- }]
-- }
EXEC sp_OAMethod @httpCurl, 'DoYourThing', @success OUT, @targetCurl
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @httpCurl, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
EXEC @hr = sp_OADestroy @planJson
RETURN
END
PRINT 'Third call completed.'
PRINT 'No dependency resolution was needed because all inputs were already known.'
EXEC @hr = sp_OADestroy @httpCurl
EXEC @hr = sp_OADestroy @jsonOAuth2
EXEC @hr = sp_OADestroy @planJson
END
GO