SQL SERVER 2005: Get a table from string list

Hi,

 Below mentioned function will convert a given list of strings into a table format and will return a table.though its not a perfect optimized as varchar takes only (8000) chars.If solution for further improvement available will be heartily appreciated


GO
IF OBJECT_ID('testing.dbo.funGetTableFromList') IS NOT NULL
DROP FUNCTION funGetTableFromList;
GO
CREATE FUNCTION funGetTableFromList
(
@varSeprator VARCHAR(1),
@varString VARCHAR(MAX)
)
RETURNS @ResultTable TABLE
(
ResultString VARCHAR(4000)
)
AS
BEGIN
DECLARE @value VARCHAR(MAX);
DECLARE @Del char(1);

SET @Del=@varSeprator;
SET @varString=@varString+@Del;

WHILE charindex(@Del,@varString,0)<>0

BEGIN

SELECT @value=RTRIM(LTRIM(SUBSTRING(@varString,1,CHARINDEX(@Del,@varString,0)-1))),
@varString=RTRIM(LTRIM(SUBSTRING(@varString,CHARINDEX(@Del,@varString,0)+1,len(@varString))));
 
INSERT INTO @ResultTable(ResultString) VALUES(@value);

END

RETURN
END

Make a Call :
SELECT * from  dbo.funGetTableFromList('|','one|two|Kamesh|Threee|you|me|we|group|part Oracle|DB|Mysql');


Thanks and regards
Kamesh shah

SQL SERVER 2005 - Procedure to create Batch with start and end time for particular days

Hi,
This logic will help you to generate the batches for the days for particular time spans and you can use this to generate batches with particular details

Pass the arguments of Start Date,End Date,Start Time,End Time and give the time interval in minutes and it will generate the result



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ListTheDate

@varFromDate   VARCHAR(50),-- from date
@varToDate VARCHAR(50),-- to date
@varTimePartitionInMinute VARCHAR(50),-- time span in minutes
@PartStartTime VARCHAR(50),-- daily start time
@PartEndTime VARCHAR(50)-- daily end time

AS
BEGIN
DECLARE  @MidDate TABLE
(
MidDates DATETIME,
StartTime DATETIME,
EndTime DATETIME,
Name VARCHAR(50)
);
DECLARE @lCount INTEGER;
SET @lCount =0;
DECLARE @lDays INTEGER;
DECLARE @lTime INTEGER;
DECLARE @lCountTime INTEGER;

SET NOCOUNT ON;

SELECT @lDays=DATEDIFF(day,CONVERT(DATETIME,@varFromDate,103),CONVERT(DATETIME,@varToDate,103));

SELECT @lTime=DATEDIFF(minute,CONVERT(DATETIME,@PartStartTime,108),CONVERT(DATETIME,@PartEndTime,108));
WHILE (@lDays >= @lCount)
BEGIN

SET @lCountTime =0;
WHILE(@lTime > @lCountTime)

BEGIN
INSERT INTO @MidDate
(
MidDates,
StartTime,
EndTime,
Name
)
VALUES
(
DATEADD(day,@lCount,CONVERT(DATETIME,@varFromDate,103)),
DATEADD(minute,@lCountTime,CONVERT(DATETIME,@PartStartTime,108)),
DATEADD(minute,@lCountTime+@varTimePartitionInMinute,CONVERT(DATETIME,@PartStartTime,108)),
'Kamesh'
);

SELECT @lCountTime = @lCountTime + @varTimePartitionInMinute;

END

SELECT @lCount = @lCount + 1;

END

SELECT CONVERT(VARCHAR(15),MidDates,103) AS "Date",
Name,
CONVERT(VARCHAR(15),StartTime,108) AS "Batch Start Time",
CONVERT(VARCHAR(15),EndTime,108) AS "BATCH END TIME"

FROM @MidDate;


END
GO