Looping

What is the end goal with the data. How is the data built. Where are you querying this?

Cursor is one way to go through results.

Hi Arnold

The query executes the stored procedure called Freak_APITest2 using variables, the code in Freak_APITest2 is below. I need the stored procedure to be executed based on the number of rows in the table Scoreboard. I hope this makes sense?

alter procedure [dbo].[Freak_APITest2]
(
 @LeaderboardName			varchar		(20)
, @LeaderboardNameFull		varchar		(20) 
, @PlayerCount				varchar		(20)
, @PlayerName				varchar		(20)
, @Score					nvarchar	(20)
, @ZombiesKilled			varchar		(15)
, @Accuracy				varchar		(50)
, @DamageTaken				varchar		(20)
--, @SecondsInLevel			varchar		(30)
, @Deaths					varchar		(50)
 --,@Date					datetime	
)
as

--DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);

DECLARE @key varchar(100) = 'hidden';DECLARE @authHeader varchar(64) = 'Bearer '+@key; 


SET @contentType = 'application/json';
SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName": "' + @LeaderboardName + '",
        "LeaderboardNameFull": "' + @LeaderboardNameFull + '",
        "PlayerCount":  ' + @PlayerCount + ',
        "PlayerName": "' + @PlayerName + '",
        "Score": ' + @Score + ',
        "ZombiesKilled": ' + @ZombiesKilled + ',
        "Accuracy":  ' + @Accuracy + ',
        "DamageTaken":  ' + @DamageTaken + ',
        "Deaths":  ' + @Deaths + '
      }
    }
  ]
}'

SET @url = 'https://api.airtable.com/v0/appvFNyUQi9WZWSFH/ScoreTestTable';



-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

Select allOpen in new window

Look at cursor.

To simplify things scoreboard is the middle?

Look at cursor
Perhaps I am not clear.
You seem to have a process in mind and you want a change.

How is the scoreboard table, query created.

A cursor define whose contents are from a select * scoreboard
You can go row by row of the response.

Can you give me an example?

See cursor example
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver15

DECLARE @LeaderboardName NVarChar(200), ...
DECLARE cr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT LeaderboardName, ... FROM Scoreboard
OPEN cr
WHILE 1=1 BEGIN
      FETCH NEXT FROM cr INTO @LeaderboardName, ...
      IF @@FETCH_STATUS <> 0 BREAK
       EXEC [Freak_APITest2]  @LeaderboardName, ... -- One EXEC per row
END
CLOSE cr
DEALLOCATE cr

Note replace ... with the other columns/variables you need to put in EXEC.

Thank you all, appreciate all your help and patience!