question about functions

[notlimah]

when i try to complile this functions on sql server 7

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS


BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
-- following line added 10/06/04 as null
-- values cause issues
IF @String IS NULL RETURN
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END

RETURN
END

i get this error below

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 14
Must declare the variable '@String'.
Server: Msg 137, Level 15, State 1, Line 20
Must declare the variable '@Delimiter'.
Server: Msg 137, Level 15, State 1, Line 23
Must declare the variable '@STRING'.
Server: Msg 137, Level 15, State 1, Line 25
Must declare the variable '@STRING'.
Server: Msg 137, Level 15, State 1, Line 29
Must declare the variable '@STRING'.
Server: Msg 137, Level 15, State 1, Line 31
Must declare the variable '@STRING'.


but why cannot some explain to me plz

 

[JamesLean]

User-defined functions are only available in SQL 2000

--James

 

[ESquared]

But there's nothing that says you cannot use this code inline, in a stored procedure. Just use a temp table instead of a table variable.

And for what it's worth, I think using an index and stepping through the string is faster than actually chopping the string down each iteration. (Check out my version of the split function.) My programming experience says stepping through substrings is wayyy faster than moving and rewriting string data.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)