Invalid length parameter passed to LEFT or SUBSTRI

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

SQL Server 2005 Forums

Transact-SQL (2005)

Invalid length parameter passed to LEFT or SUBSTRI

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-22 : 10:29:31

I've been using the following code to transfer a string of comma-separated values into SQL as a parameter value:


DECLARE @userIDs nvarchar(max)
DECLARE @separator CHAR

SET @separator = ','
SET @userIDs= '5,10,15,22,32,44,57,120,156,1650,54247,'

DECLARE @separator_position INT
DECLARE @array_value nvarchar(max)
SET @separator_position = NULL
SET @array_value = NULL

DECLARE @userTable TABLE(ID INT IDENTITY(1,1), userID INT)
WHILE patindex('%' + @separator + '%' , @userIDs) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @userIDs)
		SELECT @array_value = LEFT(@userIDs, @separator_position - 1)
		INSERT INTO @userTable (userID) VALUES (@array_value)
		SELECT @userIDs = stuff(@userIDs, 1, @separator_position, '')
	END

SELECT * FROM @userTable

This has worked fine for years, but recently I also need to pass a string of comma separated dates into SQL. I am using ISO dates separated by the pipe symbol like this:

SET @dates = 2010-09-21T23:00:00|2010-09-21T23:00:00|2010-09-21T23:00:00|2010-09-21T23:00:00|'

When I run the code though, SQL (2005) throws an error:

Invalid length parameter passed to the LEFT or SUBSTRING function.

Could anyone please explain to me what's causing this, and how I can amend the code to stop it happening?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-22 : 11:02:17

Your query actually runs on my machine with your @dates once I changed the @userTable to accept dates instead of integers. Search this site for the ParseValues function, that may be help as well.

Jim

Everyday I learn something that somebody else already knew

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-09-22 : 11:27:03

Ahhhh. It was a flaming typo! That's my second one this week. I'm going to have to start taking longer breaks.

I'll still check out ParseValues though...

Thanks for your clarification :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources