Function return error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function return error

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2009-06-19 : 03:13:16

Hello Sir ,

I have Function which is return table .

I am passed ( @listofname , @Deliminator) like ( 'yaman;gupta',';')

-------------------------------------------------------------------------
Function is

alter FUNCTION [dbo].[udf_List2Table_TEST]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
@ParsedList TABLE
(
item VARCHAR(MAX)
)
AS
BEGIN

DECLARE @item VARCHAR(MAX), @Pos INT
SET @List = LTRIM(RTRIM(@List))+ @Delim
SET @Pos = CHARINDEX(@Delim, @List, 1)
WHILE @Pos > 0
BEGIN
SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @item <> ''
BEGIN
INSERT INTO @ParsedList (item)
VALUES (CAST(@item AS VARCHAR(MAX)))
END
SET @List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(@Delim, @List, 1)
END
RETURN
END

----------------------------------------------------------------------

It returns me a table like
yaman
gupta

Problem is when i am passed space ' ' at place of deliminator it gives error :- Invalid length parameter passed to the RIGHT function
like ( 'yaman;gupta',' ')

pls help me out sir

Yaman

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-19 : 03:55:26

the string does not contain the delimiter

make use of CSVTable or fnParseList

KH
[spoiler]Time is always against us[/spoiler]

yaman
Posting Yak Master

213 Posts

Posted - 2009-06-19 : 04:03:40

quote:
Originally posted by khtan

the string does not contain the delimiter

make use of CSVTable or fnParseList

KH
[spoiler]Time is always against us[/spoiler]

Space is the deliminator .
OR it is working on my local server
But it gives error on Client site Server

Yaman

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-19 : 04:06:34

don't have to re-invent the wheel. just make use of Peter's fnParseList in the link that i posted

KH
[spoiler]Time is always against us[/spoiler]

yaman
Posting Yak Master

213 Posts

Posted - 2009-06-19 : 04:46:13

quote:
Originally posted by khtan

don't have to re-invent the wheel. just make use of Peter's fnParseList in the link that i posted

KH
[spoiler]Time is always against us[/spoiler]

Thank u So much Sir
I got it ............

Yaman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources