Solved

convert to SQL Server loop

Posted on 2008-10-09
4
200 Views
Last Modified: 2010-03-19
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
Comment
Question by:dotnet0824
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
 

Author Comment

by:dotnet0824
Comment Utility
thanks a lot mwvisa.. I posted a query to add records to temp table if you could help.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
You are welcome.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now