Solved

Passing VarChar(Max) to a Table Function

Posted on 2010-11-25
3
515 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
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Troubleshooting Methodology - steps 3 36
SqlAdvisor 2016 3 28
Upgrading Integration Services 3 28
Near realtime alert if SQL Server services stop. 20 56
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now