Sometimes, we have to pass a string of numbers separated by comma to a stored procedures / functions, and it doesn't work, throws an error like "cannot convert NVARCHAR value to interger"
e.g. string like '1001','1002','1003','1004','1005'
The problem happens here is: it replaces your comma separted list as below:
a. if it only one number, it converts to --> '1001'
b. but if it contains more than one number, then, it converts it like --> ''1001','1002','1003'', so it becomes a string, and not able to convert it to integer value.
To sort out this problem, just try this:
1. write this function as User defined function in SQL.
2. while writing query and using comma separated list e.g. say strProgIds, write/use like this:
select ufn_GetTableFromCSV(strProgIds)
It will give you comma separated values for each of the ProgIds.
--User Defined Function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Function to get a table with giving CSV as input
ALTER function [dbo].[ufn_GetTableFromCSV](@csvstr varchar(6000))
Returns @csvTable table (value varchar(500))
as
begin
DECLARE @start1 INTEGER
DECLARE @end1 INTEGER
Declare @ID varchar(500)
declare @flag bit
set @flag = 1
SET @start1 = 1
SET @end1 = 0
if(@csvstr <>'')
begin
--WHILE(@start1 < LEN(@csvstr))
WHILE(@flag = 1)
BEGIN
set @end1 = charindex(',', @csvstr, @start1)
if(@end1 != 0)
begin
SET @ID = SUBSTring(@csvstr, @start1, @end1 - @start1)
IF(@ID <> '')
BEGIN
insert @csvTable select @ID
END
SET @start1 = @end1 + 1
end
else
begin
set @flag = 0
end
END
end
--to get last substring part after the last ,
SET @ID = SUBSTring(@csvstr, @start1, len(@csvstr) + 1 - @start1)
IF(@ID <> '')
BEGIN
insert @csvTable select @ID
END
return
end
|
No responses found. Be the first to respond and make money from revenue sharing program.
|