CURSOR Function Error: Select statements included within a function cannot return data to client.

[yaknowss]

I am receiving the following error when I try to execute: Select statements included within a function cannot return data to client. My code is below. Any ideas? Thank you in advance for helping me troubleshoot.

USE [PMCLW1]
GO
/****** Object:  UserDefinedFunction [dbo].[func_PNMS_CA]    Script Date: 08/12/2013 14:40:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[func_PNMS_CA](@JobSeqid int, @SCRDET_Seqid int) 
returns varchar(5000)
as
begin
	declare @retVal varchar(5000),
			@temp varchar(1000)

	declare CACur CURSOR FOR
	(SELECT top 1 isnull (SPCOPS_Answer, '') FROM SPCOPS WHERE SCRDET_Seqid = @SCRDET_Seqid)
	SELECT b.JBSTAT_Description
		      FROM JBWORK a
				inner join JBSTAT b on a.JBSTAT_SEQID = b.JBSTAT_SEQID
		      WHERE  a.PRJOBS_SEQID = @JobSeqid
		      and a.dptmnt_seqid=7
	
	OPEN CACur

	FETCH NEXT FROM CACur 
	INTO @temp

	set @retVal = ''
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		IF len(@retVal) = 0 set @retVal = @temp
		ELSE set @retVal = @retVal + ', ' + @temp
		
		FETCH NEXT FROM CACur 
		INTO @temp
	END
	
	close CACur
	deallocate CACur
	
	return @retVal
end

 

[yelworcm]

the following is not part of the cursor, it appears:

SELECT b.JBSTAT_Description
		      FROM JBWORK a
				inner join JBSTAT b on a.JBSTAT_SEQID = b.JBSTAT_SEQID
		      WHERE  a.PRJOBS_SEQID = @JobSeqid
		      and a.dptmnt_seqid=7

If you are just getting a single row, why are you using the cursor? In older versions of SQL Server, I have seen a cursor like that add a good 3 seconds on the execution time.

 

[yaknowss]

Good question... This was recommended by my Sr. Web Developer.

 

[TheBugSlayer]

Something is not right, to back up Yelworcm. Maybe the second statement is the only relevant one...Do you need both input parameters? Maybe you need to join the table in the first statement to the second statement. In any case, that is what you need to sort out. Both queries return one single field, so either one works with the cursor. The first one only returns one row (TOP 1)...maybe you need to store that value into a variable and concatenate it with the value...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[yaknowss]

I figured it out after joining the two tables. Thanks everyone!