Split Function - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Split Function

Author

Topic

MatiTuk
Starting Member

3 Posts

Posted - 2011-07-13 : 15:41:39

I am inserting an array into my database. It is passed over as a string from a vb code behind page through a parameter as roleid. I then have a stored procedure. I use the statement to access the function in the stored procedure:

Select * From OrgContactRole Where roleid IN (Select ID From fnSplitter(@roleid))

The roleid will then need to be inserted into the OrgContactRole Table

		INSERT INTO OrgContactRole(orgcontactid, roleid, creator, added,
					lastupd, bywhom)
		VALUES (@orgcontactid, @roleid, @bywhom, @now, @now, @bywhom)

The roleid will insert if there is only one roleid value, but if I have multiple I get an error that it couldn't convert from varchar to int How can I fix this?

Here is my function:

ALTER Function [dbo].[fnSplitter] (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  

Begin 
 -- Append comma

 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching

 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 

 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character

  Set @Pos2=@Pos1+1
  -- Search from the next charcater

  Set @Pos1 = @Pos1+1
 End 
 Return

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-13 : 17:49:43

In your insert statement, you are using the unsplit variable, which, presumably is in the form "11,22,77" which cannot be converted to a number.


		INSERT INTO OrgContactRole(orgcontactid, roleid, creator, added,
					lastupd, bywhom)
		VALUES (@orgcontactid, @roleid, @bywhom, @now, @now, @bywhom)

If there is only one number, this of course would work correctly because there are no commas. What you need is probably something like this:

INSERT INTO OrgContactRole(orgcontactid, roleid, creator, added,
					lastupd, bywhom)
select @orgcontactid, id, @bywhom, @now, @now, @bywhom FROM dbo.fnSplitter(@roleid)

That would insert the split values for ID, but for each of the other columns, it would insert the same value contained in the corresponding variable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources