Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Read record one by one and pass the value to UDF function

Hi,

This is for SQL SERVER not MySQL Server, sorry.

I have a UD function tha returns a table. I need to take one value from each row and pass to other function or stored proc. Should I use a FORWARD ONLY cursor to achieve it? If so the number of recods won't be more then 1000.

Here is the code to work with sample data. I need to get each ScheduledStartDate value where the Frequency = 'Daily' and pass as parameter to other function or stored proc.
 
DECLARE @StartWeekDate DATETIME
DECLARE @EndWeekDate DATETIME

SET @StartWeekDate = '03/26/2011'
SET @EndWeekDate = '04/05/2011'

DECLARE @TempTable1 TABLE
(ScheduledStartDate DATETIME,
ScheduledStartTime VARCHAR(8),
ScheduledStartDay CHAR(3),
Frequency VARCHAR(8),
Interval INT,
EndWeekDate DATETIME)


INSERT INTO @TempTable1
SELECT '2011-03-26 05:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-26 02:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-29 18:00:00:000','6:00PM','Tue', 'Daily', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-04-03 21:00:00:000','9:00PM','Sun', 'Daily', 1, '2011-04-05 00:00:00:000' 

SELECT * FROM @TempTable1

Open in new window

0
smano
Asked:
smano
  • 12
  • 6
  • 4
2 Solutions
 
smanoAuthor Commented:
Or if someone can help to complete and if not give suggestion - even better. I need to construct a final table that include the previous data and dynamically adds additional records based on ScheduleStartDate and EndWeekDate where Frequency = 'Daily'.

Here is the .doc whats needed.
 Needs-to-be-done.docx
0
 
markterryCommented:
This is doable withing a procedure. I am working on the code right now.
0
 
smanoAuthor Commented:
Ok, not sure how synthax looks in MySQL since I made a mistake for posting under MySQL and should be under SQL Server which I added.

I also made a function that dynamically adds days between ScheduleStartDate and EndWeekDate
;WITH NumberOfDays AS
(
SELECT 0 as d
UNION ALL
SELECT d + 1
FROM NumberOfDays
WHERE d < DATEDIFF(dd,@StartDate, @EndDate)
)
SELECT
DATEADD(day,DATEDIFF(day,0,@EndDate),-d) AS [Day]
FROM NumberOfDays

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
markterryCommented:
I didnt actually end up writing a procedure. Here is a quick loop that demonstrates how to do it. If you want to make a procedure I can show you how that is done as well.

 
--DECLARE @StartWeekDate DATETIME
--DECLARE @EndWeekDate DATETIME

--SET @StartWeekDate = '03/26/2011'
--SET @EndWeekDate = '04/05/2011'

DECLARE @TempTable1 TABLE
(ScheduledStartDate DATETIME,
ScheduledStartTime VARCHAR(8),
ScheduledStartDay CHAR(3),
Frequency VARCHAR(8),
Interval INT,
EndWeekDate DATETIME)


INSERT INTO @TempTable1
SELECT '2011-03-26 05:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-26 02:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-29 18:00:00:000','6:00PM','Tue', 'Daily', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-04-03 21:00:00:000','9:00PM','Sun', 'Daily', 1, '2011-04-05 00:00:00:000' 

SELECT * FROM @TempTable1

DECLARE 
@ScheduledStartDate DATETIME,
@ScheduledStartTime VARCHAR(8),
@ScheduledStartDay CHAR(3),
@Frequency VARCHAR(8),
@Interval INT,
@EndWeekDate DATETIME

DECLARE InsertCursor CURSOR FOR
SELECT * FROM @TempTable1 Where Frequency = 'Daily';
OPEN InsertCursor;
FETCH NEXT FROM InsertCursor
INTO 
@ScheduledStartDate ,
@ScheduledStartTime ,
@ScheduledStartDay ,
@Frequency ,
@Interval ,
@EndWeekDate 
WHILE @@FETCH_STATUS = 0
   BEGIN
	  WHILE @ScheduledStartDate < @EndWeekDate
	  BEGIN
	  Select @ScheduledStartDate = DATEADD(d,1,@ScheduledStartDate)
	  INSERT INTO @TempTable1 Select @ScheduledStartDate , @ScheduledStartTime ,@ScheduledStartDay ,@Frequency ,@Interval ,@EndWeekDate		
      END
      FETCH NEXT FROM InsertCursor;
   END;
CLOSE InsertCursor;
DEALLOCATE InsertCursor;

SELECT * FROM @TempTable1

Open in new window

0
 
markterryCommented:
My cursor was for MS SQL.

Your CTE query works as well, will need to be altered a bit for insertion ofcourse.

0
 
markterryCommented:
If you are doing this on large amounts of data, I would go with your CTE query.
0
 
smanoAuthor Commented:
I think it will work. Don't think would be mor then 1000 records TOTAL.
0
 
smanoAuthor Commented:
yes if I can have it as a complete proc.. it would be great
0
 
smanoAuthor Commented:
And actually SheduledStartDay also should be incremented by 1 day but that's fine I'll fix it.
0
 
smanoAuthor Commented:
Fixed the ScheduledStartDay so it shows up incrementing the day name also. However, only 6:00PM data changes but 9:00PM data is not
 
DECLARE @TempTable1 TABLE
(ScheduledStartDate DATETIME,
ScheduledStartTime VARCHAR(8),
ScheduledStartDay CHAR(3),
Frequency VARCHAR(8),
Interval INT,
EndWeekDate DATETIME)


INSERT INTO @TempTable1
SELECT '2011-03-26 05:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-26 02:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-29 18:00:00:000','6:00PM','Tue', 'Daily', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-04-03 21:00:00:000','9:00PM','Sun', 'Daily', 1, '2011-04-05 00:00:00:000' 

SELECT * FROM @TempTable1

DECLARE 
@ScheduledStartDate DATETIME,
@ScheduledStartTime VARCHAR(8),
@ScheduledStartDay CHAR(3),
@Frequency VARCHAR(8),
@Interval INT,
@EndWeekDate DATETIME

DECLARE InsertCursor CURSOR FOR
SELECT * FROM @TempTable1 Where Frequency = 'Daily';
OPEN InsertCursor;
FETCH NEXT FROM InsertCursor
INTO 
@ScheduledStartDate,
@ScheduledStartTime,
@ScheduledStartDay,
@Frequency,
@Interval,
@EndWeekDate 
WHILE @@FETCH_STATUS = 0
   BEGIN
	  WHILE @ScheduledStartDate < @EndWeekDate
	  BEGIN
	  Select @ScheduledStartDate = DATEADD(d,1,@ScheduledStartDate),
	  @ScheduledStartDay = DATENAME(DW,@ScheduledStartDate)
	  INSERT INTO @TempTable1 Select @ScheduledStartDate , @ScheduledStartTime ,@ScheduledStartDay ,@Frequency ,@Interval ,@EndWeekDate		
      END
      FETCH NEXT FROM InsertCursor;
   END;
CLOSE InsertCursor;
DEALLOCATE InsertCursor;

SELECT * FROM @TempTable1

Open in new window

0
 
smanoAuthor Commented:
The only oustanding issue is only 6:00PM data changes but 9:00PM data is not
0
 
_agx_Commented:
What was wrong with your original function? Just wondering why you're using a cursor now instead.
0
 
smanoAuthor Commented:
I didn't have any function ;-).
 Right now the cursor reads each ScheduledStartDate and increment by 1 along with ScheduledStartDay  however it doesn't changes the 9:00PM record. Only the 6PM data changes
0
 
smanoAuthor Commented:
Ok, I see what you asking agx. The function I have just returns the TABLE data which I need to take and continue. For the above example I parsed the data from that table.
0
 
_agx_Commented:
lol. That was your term http://#a35707929 ;-)  Seems like you should be able to tweak it, so you don't need a cursor.  This example doesn't give you everything, but notice how it generates the daily dates? You could easily combine it with the "once" records.

DECLARE @TempTable1 TABLE
(ScheduledStartDate DATETIME,
ScheduledStartTime VARCHAR(8),
ScheduledStartDay CHAR(3),
Frequency VARCHAR(8),
Interval INT,
EndWeekDate DATETIME)


INSERT INTO @TempTable1
SELECT '2011-03-26 05:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-26 02:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-29 18:00:00:000','6:00PM','Tue', 'Daily', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-04-03 21:00:00:000','9:00PM','Sun', 'Daily', 1, '2011-04-05 00:00:00:000' 

;WITH CTE (ScheduledStartDate, ScheduledStartTime, Frequency, Interval, EndWeekDate)
AS
(
Select ScheduledStartDate, ScheduledStartTime, Frequency, Interval, EndWeekDate 
FROM   @TempTable1
WHERE  Frequency = 'Daily' AND Interval = 1
    UNION ALL 
    SELECT DATEADD(d, 1, ScheduledStartDate), ScheduledStartTime, Frequency, Interval, EndWeekDate
    FROM CTE 
    WHERE DATEADD(d, 1, ScheduledStartDate) <= EndWeekDate
)
SELECT ScheduledStartDate, dateName(dw, ScheduledStartDate) AS ScheduledStartDay, 
		Frequency, Interval, EndWeekDate
FROM   CTE
ORDER BY ScheduledStartDate

Open in new window


0
 
_agx_Commented:
Whoops .. our posts crossed.
0
 
_agx_Commented:
Small correction

Seems like this gives you the desired data in the .docx file (for "daily" records.)  But let me know if I'm off base.

DECLARE @TempTable1 TABLE
(
ScheduledStartDate DATETIME,
ScheduledStartTime VARCHAR(8),
ScheduledStartDay CHAR(3),
Frequency VARCHAR(8),
Interval INT,
EndWeekDate DATETIME)


INSERT INTO @TempTable1
SELECT '2011-03-26 05:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-26 02:00:00:000','5:00AM','Sat', 'Once', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-03-29 18:00:00:000','6:00PM','Tue', 'Daily', 1, '2011-04-05 00:00:00:000' UNION
SELECT '2011-04-03 21:00:00:000','9:00PM','Sun', 'Daily', 1, '2011-04-05 00:00:00:000' 

;WITH CTE (BaseStartDate, ScheduledStartDate, ScheduledStartTime, Frequency, Interval, EndWeekDate)
AS
(
SELECT ScheduledStartDate AS BaseStartDate, ScheduledStartDate, ScheduledStartTime, Frequency, Interval, EndWeekDate 
FROM   @TempTable1
WHERE  Frequency = 'Daily' AND Interval = 1
    UNION ALL 
    SELECT BaseStartDate, DATEADD(d, 1, ScheduledStartDate), ScheduledStartTime, Frequency, Interval, EndWeekDate
    FROM CTE 
    WHERE DATEADD(d, 1, ScheduledStartDate) <= dateAdd(d, 1, EndWeekDate)
)
SELECT  ScheduledStartDate, Left(dateName(dw, ScheduledStartDate), 3) AS ScheduledStartDay, 
		Frequency, Interval, EndWeekDate
FROM   CTE
ORDER BY BaseStartDate, ScheduledStartDate

Open in new window

0
 
smanoAuthor Commented:
Ahh, ok. I guess I'm just tired and two heads or three better then one, right?.

Both of you guys get points. Thanks!
0
 
_agx_Commented:
two heads or three better then one, right?.

umm... unless you're talking greek mythology..then it just gets scary ;-)
0
 
smanoAuthor Commented:
Yep, agx.. This is it. Props!
I learned something today from both of you guys!!!
0
 
smanoAuthor Commented:
Greek, mythology, of course :-). Take care.
0
 
_agx_Commented:
You too :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now