Click here to Skip to main content
14,919,896 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am using this code but its not working,it just update only one value.I want to update mulitple rows with different values
SQL
USE [DB_QSO_Updated]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 06/23/2014 13:44:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
		
	Id int identity(1,1),
	Value nvarchar(100)
) 
AS  
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select 
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
 Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

use in store procedue:
SQL
select * from dbo.Split(@RunTimeValue,',')
Posted
Updated 22-Jun-14 22:23pm
v2

1 solution

Probably, it's your insert statement:
SQL
Insert Into @RtnValue (value)
Since you don't specify the columns, it will try to insert your value into the first column - which is identity - and fail because:
1) You can't write to an an Identity column
and
2) You can't write a null into your Value column.

Try specifying the columns:
SQL
INSERT INTO @RtnValue (Value) VALUES (value)

Or see here: Converting comma separated data in a column to rows for selection[^] which does much the same thing.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900