January 25, 2006 at 4:53 am
Hi All,
I Tried This Code
create function test1(
@IDS varchar(100))
returns int
as
begin
Declare @Tot int
Select @Tot =Count(*) From FeatureConfig where Id in (@IDS)
return @Tot
end
go
Declare @IDs varchar(100)
Set @IDs = '10,12,14,16'
Select dbo.test1(@IDs)
Which Gives me error
Server: Msg 245, Level 16, State 1, Procedure test1, Line 8
Syntax error converting the varchar value '10,12,14,16' to a column of data type int.
This Error must be comming as id list is in varchar. it should be as result set. I do not want to convert it into result set inside my function as this function executes in a select quesry which will make this execute that maney times.
please suggest if any solution. like if i could put ids in some table or somewhere else
Thanks
Vijay Soni
January 25, 2006 at 5:34 am
Try passing a table variable with the values instead of a varchar.
There is a good explanation of this here:
http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp
HTH,
Bill Mell
January 26, 2006 at 7:40 am
Create the following function:
CREATE FUNCTION udf_Generate_Inlist_to_Table(
@list varchar(8000))
RETURNS @tbl TABLE (listitem varchar (200) not null)
AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= DATALENGTH(@list)/2
BEGIN
SET @chunklen = 4000 - DATALENGTH(@leftover) / 2
SET @tmpstr = LTRIM(@leftover + SUBSTRING(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = CHARINDEX(',',@tmpstr)
WHILE @pos > 0
BEGIN
SET @STR = SUBSTRING(@tmpstr,1,@pos-1)
INSERT @tbl (listitem) VALUES(@str)
SET @tmpstr = LTRIM(SUBSTRING(@tmpstr,@pos+1,LEN(@tmpstr)))
SET @pos = CHARINDEX(',',@tmpstr)
END
SET @leftover = @tmpstr
END
IF LTRIM(RTRIM(@leftover)) <> ''
INSERT @tbl (listitem) VALUES (@leftover)
RETURN
END
GO
Use it in the following way:
DECLARE @my_list varchar(8000)
SET @my_list = 'abc,def,xyz'
SELECT listitem
FROM dbo.udf_Generate_Inlist_to_Table(@my_list)
The results are:
abc
def
xyz
This works really great. I want to give thanks to the person I got it from somewhere here on SQLServerCentral.com but I can't remember who it is (sorry).
Good luck!
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 26, 2006 at 10:58 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply