executing function within Stored Proc

• neens_1602

  SSC Rookie

  Points: 31

  More actions

  October 5, 2009 at 9:56 pm

  #226856

  one of the input parameters to the stored procedure: @arStatus as text=Null,

  -- Convert the XML into a Table:

  DECLARE @tblStatus TABLE(Statustype varchar(50))

  IF @arStatus IS NOT NULL

  BEGIN

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @arStatus

  DECLARE @item varchar(50)

  INSERT INTO @tblStatus

  SELECT d

  FROM OPENXML(@hDoc, N'/r/i', 1)

  with (d varchar(50))

  EXEC sp_xml_removedocument @hDoc

  END

  --

  DECLARE @status varchar(50)

  DECLARE @statuslist varchar(300)

  SET @statuslist=''

  DECLARE status_cursor CURSOR FOR

  SELECT Statustype

  FROM @tblStatus

  OPEN status_cursor

  FETCH NEXT FROM status_cursor INTO @status

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @statuslist = COALESCE(@statuslist + ',','')+ CAST (@Status As VARCHAR)

  SELECT SUBSTRING(@statuslist, 2, Len(@statuslist))

  FETCH NEXT FROM status_cursor INTO @status

  END

  CLOSE status_cursor

  DEALLOCATE status_cursor

  SELECT @statuslist;

  ---

  AND (@arStatus Is Null

  OR dbo.at_ace_EducationStatus(E.ACE_Education_Id, @End_Date)in (@statuslist)) -- I think the problem is occurring here

  Could anyone please help

• Lowell

  SSC Guru

  Points: 323547

  More actions

  October 5, 2009 at 10:04 pm

  #1062086

  in (@statuslist))

  i think that is your problem; that is the same as saying in 'A single string that happens to have a comma in it')

  which is not what you were intending. you need to grab one of the many Split functions in the script contribution section and use that to split your string into a table variable:

  in dbo.Split(@statuslist)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!