[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

convert to SQL Server loop

Hi,
I have this code which has to be written in SQL Server stored Proc . Basically stored proc accepts
StartDate and EndDate and Calcuates the days and loops thru the end of the date and runs the select statement .
   S = DateDiff("d", varStartDate, varEndDate - 1)
                If s > 0 Then
                    For i = 1 To s
                                          
                      Select * from tblBank where bankID = 1 and date = dateAdd("d",s,varstartdate)
                      and bankID = varbankID
0
dotnet0824
Asked:
dotnet0824
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
First let me suggest, you just do this with a normal query, but I will post a loop shortly:
(seems like you are adding a day at beginning and end, so you technically can just input startdate one day later and end date one day sooner -- but coded per loop above)
Select * 
from tblBank 
where bankID = @varbankID
and date between (@varstartdate+1) and (@varenddate-1)
-- and bankID = 1 /* commented this out as already filtering bankID */

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Here is loop:
WHILE (@varstartdate+1) <= (@varenddate-1)
BEGIN
 
Select * 
from tblBank 
where bankID = @varbankID
and date = (@varstartdate+1)
 
SET @varstartdate = @varstartdate+1
 
END

Open in new window

0
 
dotnet0824Author Commented:
thanks a lot mwvisa.. I posted a query to add records to temp table if you could help.
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now