Link to home
Start Free TrialLog in
Avatar of Stevie Zakhour
Stevie Zakhour

asked on

Looping

Hi Guys

I have a MS SQL table called Scoreboard, it currently has 9 rows. See below

User generated image
I put together a query that reads from the table Scoreboard, see below. I want to run this query 9 times until it has read all 9 rows. How do I do this?

DECLARE @LeaderboardName		varchar		(20)
DECLARE @LeaderboardNameFull	varchar		(20) 
DECLARE @PlayerCount			varchar		(20)
DECLARE @PlayerName				varchar		(20)
DECLARE @Score					nvarchar	(20)
DECLARE @ZombiesKilled			varchar		(15)
DECLARE @Accuracy				varchar		(50)
DECLARE @DamageTaken			varchar		(20)
DECLARE @Deaths					varchar		(50)

Select	 @LeaderboardName        = LeaderboardName		from #Temp		
Select   @LeaderboardNameFull	 = LeaderboardNameFull	from #Temp		
Select   @PlayerCount	 = PlayerCount					from #Temp		
Select	 @PlayerName	 = PlayerName					from #Temp		
Select	 @Score	         = Score						from #Temp			
Select	 @ZombiesKilled	 = ZombiesKilled				from #Temp		
Select   @Accuracy	     = Accuracy						from #Temp				
Select	 @DamageTaken	 = DamageTaken					from #Temp		
Select  @Deaths			 = Deaths						from #Temp			


EXEC [Freak_APITest2]  @LeaderboardName, @LeaderboardNameFull,@PlayerCount, @PlayerName, @Score,@ZombiesKilled,@Accuracy,@DamageTaken,@Deaths

Open in new window


Any help is greatly appreciated.
Avatar of arnold
arnold
Flag of United States of America image

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.
Avatar of Stevie Zakhour
Stevie Zakhour

ASKER

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);

Open 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?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!