quote:

---

Originally posted by tkizer

Do you see a yellow arrow when debugging the stored procedure? If not, then it's not working correctly.

Tara Kizer
aka tduggan

---

quote:

---

Originally posted by surendrakalekar
No it is not showing me yello arrow while debugging. What could be the problem?

---

quote:

---

Originally posted by tkizer

According to BOL, the debugger is available for stored procedures.

---

quote:

---

Originally posted by tkizer

You use PRINT statements, but outside of the UDF. You can post a very simple one and not your actual one.

---

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fnSplit')
	DROP FUNCTION fnSplit
GO

------------------ fnSplit ----------------------

CREATE FUNCTION dbo.fnSplit
	(@RowData nvarchar(4000),
	@SplitOn nvarchar(5))

-- AUTHOR: MI 11/30/05
--	Adapted from Seventhnight's posting on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648.
--	The function preserved starting and trailing spaces.
--	IMPORTANT: Current does NOT support spaces as delimiters. 
-- PURPOSE: 
--	Split a uniformly delimited string into a table.
-- PARAMETERS:
--	@RowData: the string to split.
--	@SplitOn: the delimiter.
-- RETURNS:
--	Table with 2 columns: counter and Data.
-- EXAMPLE 1:
-- 	SELECT * FROM dbo.fnSplit 
-- 		('Test|string||that needs to|be||split', 
-- 		 '|')
-- 	GO
-- EXAMPLE 2:
-- 	SELECT * FROM dbo.fnSplit 
-- 		('  Test\\\   string\\\that needs to  \\\\\\ be \\\   split    \\\\\\', 
-- 		 '\\\')
-- 	GO

RETURNS @RtnValue table 
	(Id int identity(1,1),
	Data nvarchar(4000)) 
AS  
BEGIN 
	-- Ensure that the SplitOn parameter has at least 1 character. If not,
	-- we would loop infinitely
	IF Len(@SplitOn) < 1
	BEGIN
-- 		RAISERROR ('Please provide a valid character in the %d parameter.',
-- 		      16, 1, '@SplitOn')
		Return
	END
	ELSE
	BEGIN
		Declare @Cnt int
		Declare @sValue nvarchar(4000)
		Set @Cnt = 1
	
		While (Charindex(@SplitOn,@RowData)>0)
		Begin
			(Select @sValue = Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))	-- removed LTrim(RTrim())
			
			Insert Into @RtnValue (data)
				Select Data = @sValue
	
			Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+Len(@SplitOn),len(@RowData))
			Set @Cnt = @Cnt + 1
		End
	
		Select @sValue = @RowData	-- removed LTrim(RTrim())
		Insert Into @RtnValue (data)
		Select Data = @sValue
	END
	Return
END

GO


-- =============================================
-- Example to execute function
-- =============================================
SELECT * FROM dbo.fnSplit 
	('  Test\\\   string\\\that needs to  \\\\\\ be \\\   split    \\\\\\', 
	 ' ')
GO

DECLARE @RowData nvarchar(4000), @SplitOn nvarchar(5)

SET @RowData = '  Test\\\   string\\\that needs to  \\\\\\ be \\\   split    \\\\\\'
SET @SplitOn = '\'

DECLARE @RtnValue table (Id int identity(1,1), Data nvarchar(4000)) 

	-- Ensure that the SplitOn parameter has at least 1 character. If not,
	-- we would loop infinitely
	IF Len(@SplitOn) < 1
	BEGIN
-- 		RAISERROR ('Please provide a valid character in the %d parameter.',
-- 		      16, 1, '@SplitOn')
		Return
	END
	ELSE
	BEGIN
		Declare @Cnt int
		Declare @sValue nvarchar(4000)
		Set @Cnt = 1
	
		While (Charindex(@SplitOn,@RowData)>0)
		Begin
			(Select @sValue = Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))	-- removed LTrim(RTrim())
			
			Insert Into @RtnValue (data)
				Select Data = @sValue
	
			Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+Len(@SplitOn),len(@RowData))
			Set @Cnt = @Cnt + 1
		End
	
		Select @sValue = @RowData	-- removed LTrim(RTrim())
		Insert Into @RtnValue (data)
		Select Data = @sValue
	END


-- =============================================
-- Example to execute function
-- =============================================
SELECT * FROM @RtnValue

GO

quote:

---

Originally posted by Marioi


Why are functions so restrictive in general? Why must they be deterministic, for example?

---

quote:

---

Originally posted by tkizer
Check out the CREATE FUNCTION topic in SQL Server Books Online. You'll find the information under the "Function Determinism and Side Effects" section of that topic. Also, functions do not need to be deterministic. You just can't call nondeterministic functions within your UDF. But you can pass them to it.

---

quote:

---

Originally posted by tkizer

I was implying that you can write non-deterministic functions, not that you can call them from within a function.

---