Calling functions from SP? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Calling functions from SP?

Author

Topic

manny
Starting Member

14 Posts

Posted - 2006-02-18 : 16:49:41

I'm trying to use the following code to process a list with a delimiter(taken from http://www.sommarskog.se/arrays-in-sql.html) to return a table of sigle values back to the procedure I call it from, can someone tell me how I can actually process this returned table (I don't even know how to call the function :( ) as I want to extract the values from it and insert it into another table.

CREATE FUNCTION iter_intlist_to_table (@list ntext)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen

SET @pos = charindex(' ', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (number) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(' ', @tmpstr)
END

SET @leftover = @tmpstr
END

IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN
END

here the procedure i want to call it from:

CREATE PROCEDURE addvertices
@jobID int,
@XYList ntext
AS

/*here i want to call the above function and pass through XYlist as a argument and then take the returned table which and process each indivisual value i.e. loop throught the table and insert the values in to anither table*/

GO

I've spent way too long over this, and would very much appreciate any form of help.

Many thanks in advance

Manny

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-18 : 18:03:36

Manny, see if this code helps you.

This code assumes you've already compiled the function (function iter_intlist_to_table)


--call this just to see what is returned
--(it takes a <space> delimited list of integers)
select * from dbo.iter_intlist_to_table('3 4 5')
go
-----------------------------------------------------------
--create a table to test the procedure (bolow)
create table junk (xylistItem int)
go

-----------------------------------------------------------
CREATE PROCEDURE addvertices
@jobID int,
@XYList ntext 
AS

insert	junk (xylistitem)
select	[number] 
from	dbo.iter_intlist_to_table(@XYList)

GO
-----------------------------------------------------------
--call the new procedure
exec addvertices @jobid = 1, @XYList = '6 7 8'

--see if the insert worked
select * from junk
go

--clean up
drop table junk
drop proc addvertices

Be One with the Optimizer
TG

manny
Starting Member

14 Posts

Posted - 2006-02-19 : 13:49:30

Many thanks for that TG. The examples provided have been of great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources