We help IT Professionals succeed at work.

Looping

Hi Guys

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

Snip20200205_19.png
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.
Comment
Watch Question

Distinguished Expert 2019

Commented:
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.

Author

Commented:
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

Distinguished Expert 2019

Commented:
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.

Author

Commented:
Can you give me an example?
Distinguished Expert 2019

Commented:
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

Here ya' go:


 
DECLARE @Accuracy               varchar(50)  
DECLARE @DamageTaken            varchar(20)  
DECLARE @Deaths                 varchar(50)  
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 cursor_scoreboard CURSOR LOCAL FAST_FORWARD FOR 
SELECT LeaderboardName, 
    LeaderboardNameFull, 
    PlayerCount,  
    PlayerName, 
    Score, 
    ZombiesKilled, 
    Accuracy, 
    DamageTaken, 
    Deaths 
FROM dbo.Scoreboard 
ORDER BY PlayerName 
 
OPEN cursor_scoreboard 
 
WHILE 1 = 1 
BEGIN 
    FETCH NEXT FROM cursor_scoreboard INTO  
        @LeaderboardName, @LeaderboardNameFull, @PlayerCount,  @PlayerName, 
        @Score, @ZombiesKilled, @Accuracy, @DamageTaken, 
        @Deaths 
    IF @@FETCH_STATUS <> 0 
        BREAK; 
 
    EXEC dbo.[Freak_APITest2]  @LeaderboardName, @LeaderboardNameFull,@PlayerCount, @PlayerName, @Score,@ZombiesKilled,@Accuracy,@DamageTaken,@Deaths 
 
END /*WHILE*/ 
 
DEALLOCATE cursor_scoreboard 
 

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.

Author

Commented:
Thank you all, appreciate all your help and patience!