execute function in sql server 2000

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

execute function in sql server 2000

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2009-12-15 : 06:31:12

IF OBJECT_ID('[dbo].[strToTable]') IS NOT NULL
DROP FUNCTION [dbo].[strToTable]
GO
CREATE FUNCTION [dbo].[strToTable]
(
@array varchar(8000),
@del char(1)
)
RETURNS
@listTable TABLE
(
item int
)
AS
BEGIN

WITH rep (item,list) AS
(
SELECT SUBSTRING(@array,1,CHARINDEX(@del,@array,1) - 1) as item,
SUBSTRING(@array,CHARINDEX(@del,@array,1) + 1, LEN(@array)) + @del list

UNION ALL

SELECT SUBSTRING(list,1,CHARINDEX(@del,list,1) - 1) as item,
SUBSTRING(list,CHARINDEX(@del,list,1) + 1, LEN(list)) list
FROM rep
WHERE LEN(rep.list) > 0
)
INSERT INTO @listTable
SELECT item FROM rep

RETURN
END

GO

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 06:33:03

Did you have a question?

This function won't work in SQL 2000 because of the WITH statement.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-15 : 07:18:12

You want a string splitting function for 2000?

Lots of examples out there: here's one


USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[FN_Split]    Script Date: 12/15/2009 12:15:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FN_Split] (
	@text VARCHAR(8000)
	, @delimiter VARCHAR(20) = ' ')

RETURNS @Strings TABLE (
	[position] INT IDENTITY PRIMARY KEY
	, [value] VARCHAR(8000)
	)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END

As Ryan said -- your code above won't work as it uses a CTE which is only supported from 2005 and onwards. Saying that, I don't think the code above is a great use of a recursive CTE anyway especially as you haven't passed a MAXRECURSION setting to it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources