SQL function in loop issue

Question

I have been trying to get this CURSOR & WHILE LOOP working for about 2 hours now with no luck. I read a few SO threads on this and the while loop page from Microsoft, perhaps you could let me know what I am doing wrong.

I have a table which looks like this:

+-------------------------+-------------------------+
|    Case_CreatedDate     |    Case_UpdatedDate     |
+-------------------------+-------------------------+
| 2013-05-28 12:54:21.250 | 2013-07-02 08:24:02.000 |
| 2010-10-31 19:17:16.000 | 2011-02-02 14:08:04.000 |
| 2010-11-04 11:38:36.000 | 2011-01-18 12:40:15.000 |
| 2011-01-06 16:18:53.000 | 2011-01-06 16:30:25.000 |
| 2011-01-07 10:55:56.000 | 2011-01-14 09:01:40.000 |
| 2011-01-07 11:36:42.000 | 2011-01-13 11:24:03.000 |
| 2011-01-07 12:24:15.000 | 2011-01-17 16:56:41.000 |
| 2011-01-07 14:10:00.000 | 2011-02-14 09:17:55.000 |
| 2011-01-07 14:20:28.000 | 2011-01-14 10:37:20.000 |
| 2011-01-07 14:42:56.000 | 2011-01-14 14:27:41.000 |
| 2011-01-07 15:10:28.000 | 2011-01-21 11:07:50.000 |
| 2011-01-07 15:28:08.000 | 2011-01-26 11:04:27.000 |
| 2011-01-07 15:57:34.000 | 2011-01-20 15:41:43.000 |
| 2011-01-10 08:37:30.000 | 2011-01-14 09:02:43.000 |
| 2011-01-10 08:51:44.000 | 2011-01-13 15:50:26.000 |
| 2011-01-10 08:58:53.000 | 2011-01-26 11:10:54.000 |
| 2011-01-10 09:06:17.000 | 2011-01-14 09:03:33.000 |
| 2011-01-10 09:13:37.000 | 2011-01-19 15:12:07.000 |
| 2011-01-10 09:19:24.000 | 2011-01-26 11:12:37.000 |
| 2011-01-10 09:28:00.000 | 2011-03-08 16:49:59.000 |
+-------------------------+-------------------------+

I then have a function which calculates the date difference between those two dates.

I would like to use this function in conjunction with a while loop and a cursor so it will give me the results of every row when I execute it all.

At the moment, the only result I see is: "Command(s) completed successfully. " - No actual data is displaying to me.

Here is my Current query, could you please let me know where I am going wrong?

declare @CreatedDate datetime
declare @UpdatedDate datetime
declare dates CURSOR Local for
    SELECT c.Case_CreatedDate, c.Case_UpdatedDate FROM PILOT.dbo.Cases c 
    WHERE CONVERT(date, c.Case_CreatedDate, 103) >= CONVERT(date, GETDATE(), 103) 


open dates

fetch next from dates into @CreatedDate, @UpdatedDate

while @@FETCH_STATUS = 0 BEGIN

    --Execute my Function
    SELECT dbo.BusinessTurnaroundTime(@CreatedDate, @UpdatedDate)


    fetch next from dates into @CreatedDate, @UpdatedDate
END

close dates
deallocate dates

I am using SQL Server 2008.

Thanks so much, Mike

EDIT: also, dont worry about the dates not being bigger that getdate() the table I am showing you is only the top 20 records.

John Bell · Accepted Answer · 2014-07-01 12:30:12Z

1

Why are you using a while loop for this my I ask? The same result can be achieved with a simple query:

SELECT dbo.BusinessTurnaroundTime(c.Case_CreatedDate, c.Case_UpdatedDate)
FROM PILOT.dbo.Cases c 
WHERE CONVERT(date, c.Case_CreatedDate, 103) >= CONVERT(date, GETDATE(), 103)

Thanks.

answered Jul 1, 2014 at 12:30

John Bell

2,3501 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fizor Over a year ago

Son of a gun! I just Googled how to achieve what i was after and those cursors and while loops were the 1st things to pop up. Thanks for this, Im going to give it a bash.

Fizor Over a year ago

Damn it all to hell... Such a simple solution and I have been over complicating it the ENTIRE morning! Thanks so much for this Nesuke.

John Bell Over a year ago

Glad to be of service.

Tristan · Accepted Answer · 2014-07-01 12:57:26Z

1

Nesuke gave a much better solution to your problem, just in case you need to work with cursor/while again in the future, here is what was wrong with your query. You dont specify what you want done with your result. You could insert each iteration of the loop into a temp table like this:

declare @CreatedDate datetime
declare @UpdatedDate datetime
declare dates CURSOR Local for
    SELECT c.Case_CreatedDate, c.Case_UpdatedDate FROM PILOT.dbo.Cases c 
    WHERE CONVERT(date, c.Case_CreatedDate, 103) >= CONVERT(date, GETDATE(), 103) 
create table #BusinessTurnaroundTime (
    BusinessTurnaroundTime int null)

open dates

fetch next from dates into @CreatedDate, @UpdatedDate

while @@FETCH_STATUS = 0 BEGIN

    --Execute my Function
    insert #BusinessTurnaroundTime 
         dbo.BusinessTurnaroundTime(@CreatedDate, @UpdatedDate)


    fetch next from dates into @CreatedDate, @UpdatedDate
END

close dates
deallocate dates

SELECT * FROM #BusinessTurnaroundTime

answered Jul 1, 2014 at 12:57

Tristan

1,0247 silver badges14 bronze badges

1 Comment

Fizor Over a year ago

Thanks very much for still figuring that out for me +1 :)

Collectives™ on Stack Overflow

SQL function in loop issue

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related