There is probably an easier (AKA fancier) way to accomplish this,
but I tested a few numbers, and think this works the way you want things to work.
It does return a table of rows formatted the way I think you want to insert them...so theoretically, you could just call the function inline like:
Code:
INSERT FinalTable dbo.getInd(@TotalMins)
or, perhaps more in line with your purposes, the results returned by the function can be used as any "standard" table (I.e., "SELECT * from dbo.getInd" or any variation thereof).
Code:
CREATE FUNCTION getInd(@CT int)
RETURNS @CallSegments TABLE (segno int,
timeslice int,
indicator int)
AS
BEGIN
DECLARE @SegNum int
SET @SegNum = 1
IF (@CT < 200)
BEGIN
INSERT @CallSegments VALUES (@SegNum, @CT, (@CT / 100))
SET @SegNum = @SegNum + 1
END
ELSE
WHILE (@CT > 0)
BEGIN
IF ((@CT >= 200) AND (@SegNum = 1))
INSERT @CallSegments VALUES (@SegNum, 200, 4)
ELSE IF (@CT < 200)
INSERT @CallSegments VALUES (@SegNum, @CT, 6)
ELSE
BEGIN
INSERT @CallSegments VALUES(@SegNum, 100, 5)
SET @SegNum = @SegNum + 1
INSERT @CallSegments VALUES(@SegNum, 100, 5)
END
SET @SegNum = @SegNum + 1
SET @CT = @CT - 200
END
RETURN
END
Hopefully this, or a variation of it, can get you where you need to go.
dBforums
>
Please help in SQL
05-17-04, 13:22
Linear Mode
