declare @separator_position int
declare @array_value nvarchar
declare @separator char

DECLARE @numberList NVARCHAR(MAX)
SET @numberList = '23|76.25|100.36|45.54|87.14|74.25|8|98|'
SET @separator = '|'

SET @separator_position = NULL
SET @array_value = NULL
DECLARE @myTable TABLE(ID INT IDENTITY(1,1), compLevel NUMERIC(5,2))
WHILE patindex('%' + @separator + '%', @numberList) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @numberList)
		SELECT @array_value = LEFT(@numberList, @separator_position - 1)
		INSERT INTO @myTable (compLevel) VALUES (CONVERT(NUMERIC(5,2), @array_value))
		SELECT @numberList = stuff(@numberList, 1, @separator_position, '')
	END

SELECT * FROM @myTable

quote:

---

Originally posted by R

Hi all

I want to pass a list of nvarchar characters to my procedure, then import them into a temporary table as numeric(5,2) numbers.

The code below does this but the values are corrupted (e.g. 76.25 becomes 7 in the table).

Can anyone please tell me what I'm doing wrong? Thank you!

declare @separator_position int
declare @array_value nvarchar(length)
declare @separator char

DECLARE @numberList NVARCHAR(MAX)
SET @numberList = '23|76.25|100.36|45.54|87.14|74.25|8|98|'
SET @separator = '|'

SET @separator_position = NULL
SET @array_value = NULL
DECLARE @myTable TABLE(ID INT IDENTITY(1,1), compLevel NUMERIC(5,2))
WHILE patindex('%' + @separator + '%', @numberList) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @numberList)
		SELECT @array_value = LEFT(@numberList, @separator_position - 1)
		INSERT INTO @myTable (compLevel) VALUES (CONVERT(NUMERIC(5,2), @array_value))
		SELECT @numberList = stuff(@numberList, 1, @separator_position, '')
	END

SELECT * FROM @myTable

---

quote:

---

Originally posted by visakh16

Madhi has also written an article on this:-

http://sqlblogcasts.com/blogs/madhivanan/Default.aspx?PageIndex=2

---