column value to be splitted and stored in various varaibles
-
hai all,
i have a problem, its like this i have a column of varchar type and had data like
col(10,20,30,40) in one row and
col(0-10=10%,10-20=12%) and so on, i have to take each value into a local variable and has to do calculation , can anybody tell me how to store values in seperate varaible, when we do not know how many values will be stored in that column
-
This will pump each value into a #TempTable. You can alter this to pass each variable back after it is seperated or you could create your own #TempTable in your SP and pass the entire list into it, (I would recommend adding an IDENTITY key to the #TempTable so you can keep your order in the original list...
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'[dbo].[ParsePipeDelimitedList]')
AND OBJECTPROPERTY( id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ParsePipeDelimitedList]
GO
CREATE PROCEDURE ParsePipeDelimitedList
@PipeList varchar(2000),
@ColumnType varchar(7) = 'int'
AS
SET NOCOUNT ON
DECLARE @Delimiter char(1),
@LeftDelimter smallint,
@Item varchar(50),
@List varchar(8000)
CREATE TABLE #List( ItemID varchar(25) NOT NULL)
IF NOT( @PipeList IS NULL OR @PipeList = '0' OR LEN( RTRIM( @PipeList)) < 1)
BEGIN
SELECT @LeftDelimter = 1,
@Delimiter = '|',
@List = @Delimiter + @PipeList + @Delimiter
WHILE CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) > 0
BEGIN
SELECT @Item = SUBSTRING( @List, @LeftDelimter + 1, CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) - ( @LeftDelimter + 1))
IF LTRIM( RTRIM( @Item)) <> ''
BEGIN
INSERT INTO #List( ItemID) VALUES( @Item)
END
SELECT @LeftDelimter = CHARINDEX( @Delimiter, @List, @LeftDelimter + 1)
END
END
IF @ColumnType = 'int'
BEGIN
SELECT CONVERT( int, ItemID) AS 'ItemID' FROM #List
END
ELSE
BEGIN
SELECT ItemID FROM #List
END
SET NOCOUNT OFF
I wasn't born stupid - I had to study.
-
Still using the bulldozer approach???
What are the possible range of values for each parameter?
Do you need to have them converted into columns or a batch of rows?
-
yeah - I had this one laying around and answered too quickly...
I wasn't born stupid - I had to study.
-
I think it's time you changed your nickname
. -
"You keep thinkin' Sundance..., that's what you do best."
I wasn't born stupid - I had to study.
-
Maybe we should start a think tank
. -
LOL..., good one!
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply