sql server - Calling a Function in a Stored Proc Cursors -

i have function "split" calling in stored proc cursor.

my a_upload table structure is: (int, varchar(100))

upload_id   allowed_file_extensions --------------------------------------------------------------------- 1           .xls, .doc, .pdf, .docx, .xlsx, .pptx, .txt 2           .xls, .doc, .pdf, .jpeg, .jpg, .docx, .xlsx, .pptx, .txt 

table c_file_extensions is: (int, varchar(100), bit)

id  description  is_enabled --------------------------- 1   .xls         1 2   .doc         1 3   .pdf         0 4   .rtf         1 

stored proc:

declare @is_enabled varchar(10), @allowed_file_extensions varchar(100)     set @is_enabled = 'true';  declare cur cursor forward_only    select items      split((select allowed_file_extensions                    a_upload                   upload_id = 1), ',')   open cur fetch next cur @allowed_file_extensions while @@fetch_status=0 begin      if exists(select *                  c_file_extensions                 description = @allowed_file_extensions                   , is_enabled = 0)       set @is_enabled = 'false';  fetch next cur @allowed_file_extensions end close cur deallocate cur  select @is_enabled output 

function split:

create function dbo.split(@string varchar(8000), @delimiter char(1))          returns @temptable table (items varchar(8000))  begin     declare @idx int            declare @slice varchar(8000)             select @idx = 1          if len(@string) < 1 or @string null return      while @idx!= 0            begin                set @idx = charindex(@delimiter,@string)                if @idx != 0                  set @slice = left(@string,@idx - 1)                else                  set @slice = @string                   if(len(@slice)>0)               insert @temptable              (items)              values              (@slice)                   set @string = right(@string, len(@string) - @idx)                  if len(@string) = 0 break     end   return       end   

for upload_id = 1, expecting output 'false', 'true'. tried debug, , found "if exists (select * c_file_extensions description = @allowed_file_extensions , is_enabled = 0)" not working properly.

this happening because you're comparing against items leading spaces.

try this:

  select items split((select ext a_upload id = 1), ',')  

this results in:

.xls  .doc  .pdf  .docx  .xlsx  .pptx  .txt 

to solve this, could:

• modify cursor definition:

select  ltrim(items) split((select ext a_upload id = 1), ',')  

• modify dbo.split() remove leading spaces on line 29:

set @string = ltrim(right(@string,len(@string) - @idx))