0

Need to exit a loop when sql table is empty

Doing a query to determine if a table is empty and when it is I want to exit the do statement. I have tried case statements, Do while, used nulls rather than 0's, and I just can't get it to stop once the table goes empty.

doChkQ.Connection = con
doChkQ.CommandText = ("select top 1 badge from [P].[dbo].[Sub]")
doChk = doChkQ.ExecuteScalar
Do While doChk > 0

   // Code to execute

Loop

In the code I delete rows until the table is completely empty and once it is empty I want it to end the do loop.

Sonal Borkar
  • 531
  • 1
  • 6
  • 12
Paul Hays
  • 1
  • I guess the table is completely empty now. – Sonal Borkar Dec 28 '18 at 19:21
  • Why not `select COUNT(*) from [P].[dbo].[Sub]` to get an integer number representing the number of rows in the table? – Ryan Wilson Dec 28 '18 at 19:21
  • which database back-end are you using? I'm assuming sql server. I would recommend offloading the loop to a stored procedure instead. this is pure speculation of course, considering we don't have a full code sample to work with and just something I would consider best-practice – kowie le roux Dec 28 '18 at 19:26
  • I did the select count and it did the same. It will process all of the rows but when it gets to an empty SQL table it still tries to keep looping. Do I need to specify that it is an integer somewhere? – Paul Hays Dec 28 '18 at 19:52
  • See https://stackoverflow.com/questions/1999020/handling-executescalar-when-no-results-are-returned – Brian Pressler Dec 28 '18 at 21:02
  • I assume that your `// Code to execute` includes another call to `doChkQ.ExecuteScalar`? Because, if not, yeah, you will definitely have an infinite loop! – Ann L. Dec 28 '18 at 23:52
  • If all you want to do is empty the table try Truncate Table or Delete * from MyTable. Forget the loop. – Mary Dec 29 '18 at 06:17
  • Thanks guys I moved the loop to SQL and it is working much better! Great suggestion Kowie – Paul Hays Dec 30 '18 at 02:20

0 Answers0