Solved

Passing VarChar(Max) to a Table Function

Posted on 2010-11-25
3
531 Views
Last Modified: 2012-05-10
Hello,

I have been using a table function to parse a comma delimited string to a table.  The input paramter to the function is defined as VarChar(8000), I now need to change to input parameter to Varchar(Max).  

When I change the table fucntion input parameter to VarChar(max) and I execute the function the input parameter is Varchar - not Varchar(Max); only looking at the first character of the parameter.

This happens in SQL 2005 and 2008

If I change the table function input parameter to TEXT and then cast it to a varchar(max) within the function it works.

I know TEXT is going away - and I want to use Varchar(Max) as the input to a table function - what am I doing wrong?

Thanks
Kim


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Splits a Comma Delimited sting of integer values
-- =============================================
ALTER Function [BIS].[fn_SplitCommaDelimited]
(
	-- Parameters for the function
	--	@parmList varchar(8000) HOW THE FUNCTION WORKS NOW
	--	@parmList varchar(max)  WHAT I WANT TO USE
	    @parmListIn TEXT
)
RETURNS 
 @BIS_Keys TABLE 
		-- Column definitions for the TABLE variable
		(
		[RowID] [int] IDENTITY (1, 1) NOT NULL ,
		[intID] [int] NOT NULL 
		 ) 

AS
BEGIN
	
	DECLARE @parmID varchar(10)
	DECLARE @Pos bigint
	DECLARE @parmList varchar(max)

	-- NEED VARCHAR TO USE THE CODE BELOW
	SET @parmList = CAST(@parmListIn AS VARCHAR(MAX))
	
	--Parse the comma separated values into 
	-- a table variable and join the table variable 
	-- to main table in the calling SP
	
	SET @parmList = LTRIM(RTRIM(@parmList))+ ','
	SET @Pos = CHARINDEX(',', @parmList, 1)

	-- Parse The List
	IF REPLACE(@parmList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @parmID = LTRIM(RTRIM(LEFT(@parmList, @Pos - 1)))
			IF @parmID <> ''
			BEGIN
				INSERT INTO @BIS_Keys (intID) VALUES (CAST(@parmID AS int)) --Use Appropriate conversion
			END
			SET @parmList = RIGHT(@parmList, LEN(@parmList) - @Pos)
			SET @Pos = CHARINDEX(',', @parmList, 1)

		END
	END	
	RETURN 

END

Open in new window

0
Comment
Question by:KimberleyY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 32

Accepted Solution

by:
Erick37 earned 450 total points
ID: 34213491
Not having the same problem with this function.

select * from dbo.uf_Split2Int('1,2,3',',')
ALTER FUNCTION [dbo].[uf_Split2Int] 
(
	@cList varchar(max)
	,@cDelimiter varchar(5)
)  
RETURNS @SplitTable TABLE 
(
	nFieldValue int
) 
AS  
BEGIN 
	WHILE (CHARINDEX( @cDelimiter, @cList ) > 0)
	BEGIN
		INSERT INTO @SplitTable(nFieldValue)
		SELECT 
			CAST(LTRIM(RTRIM(SUBSTRING( @cList, 1, CHARINDEX( @cDelimiter, @cList ) - 1 ))) as int)

		SET @cList = SUBSTRING( @cList, CHARINDEX( @cDelimiter, @cList ) + LEN( @cDelimiter ), LEN(@cList) )
	END

	INSERT INTO @SplitTable (nFieldValue)
	SELECT LTRIM(RTRIM(@cList))

	RETURN
END

Open in new window

0
 
LVL 4

Assisted Solution

by:Andre412
Andre412 earned 50 total points
ID: 34214042
This maybe nothing but its worth checking

Check which version of sql you db is actually running under

in the management studio locate your db server, drill down to your db

right click > properties > options

look at the Compatibility level - this should be set to 2005 or above
0
 

Author Closing Comment

by:KimberleyY
ID: 34218170
Thank you both - I ended up restarting the server and it now works fine.  
Eric - thank you for the more elegant solution.  As long as I am changing things I will add the delimiter parameter as well.

Andre412 - Thank you for pointing out checking the options.  I did and they were OK.  

Thank you both - KIm
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question