This is for issues where we have to pass string input > 8000 where varchar doesnot work. We can pass text input and loop through it to populate a table.
CREATE PROCEDURE GET_TEXT_INPUT_AND_POPULATE_TABLE
@list_text text
AS
--parse comma separated varaible and put results in variable table
Declare @Tbl_ParsedTerms TABLE
(
TypeID VARCHAR(5000)
)
DECLARE @InputLen integer -- input text length
DECLARE @TextPos integer -- current position within input text
DECLARE @Chunk varchar(8000) -- chunk within input text
DECLARE @ChunkLen integer -- chunk length
DECLARE @SubStr varchar(8000)
DECLARE @IntPos integer
DECLARE @charposition integer
IF @list_text IS NOT NULL
BEGIN
SET @InputLen = DATALENGTH(@list_text)
SET @TextPos = 1
SET @ChunkLen = 1
WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
BEGIN
-- get current chunk
SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)
SET @ChunkLen = DATALENGTH(@Chunk)
BEGIN
SET @IntPos = CHARINDEX(',', @Chunk)
-- Loop Until There Is Nothing Left Of @ListofKeys
WHILE @IntPos > 0
BEGIN
-- Extract The String
SET @SubStr = SUBSTRING(@Chunk, 0, @IntPos)
-- Insert The String Into The Table
if @SubStr <> ''
INSERT INTO @Tbl_ParsedTerms (TypeID) VALUES (@SubStr)
-- Remove The String & Comma Separator From The Original
SET @Chunk = SUBSTRING(@Chunk, LEN(@SubStr) + 2, LEN(@Chunk) - LEN(@SubStr) + 1)
-- Get The New Index To The String
SET @IntPos = CHARINDEX(',', @Chunk)
END
-- Return The Last One
if @Chunk <> ''
INSERT INTO @Tbl_ParsedTerms (TypeID) VALUES (@Chunk) -- get current chunk
SET @TextPos = @TextPos + @ChunkLen
END
end
select * from @Tbl_ParsedTerms
end
Above SP GET_TEXT_INPUT_AND_POPULATE_TABLE when used as
EXEC GET_TEXT_INPUT_AND_POPULATE_TABLE '1,2,3' gives a table with 3 rows with values as 1, 2 and 3
|
| Author: Kapil Dhawan 17 Jun 2008 | Member Level: Gold Points : 2 |
Hello
Nice piece of code
Thanks for sharing your knowledge with us.
I hope to see more good code from your side
This code will help lots of guys
Thanks to you
Regards,
Kapil
|