ALTER FUNCTION [dbo].[pnumsforodid]( @OdID INT ) 
RETURNS VARCHAR(1024) 
AS
BEGIN
	DECLARE @InvoiceIDs varchar(1024), @invoiceID varchar(6),@InvoiceCount int
	DECLARE @PnumList varchar(1024), @PNUM varchar(8), @PNUMCOUNT INT
	
	DECLARE @InvoiceList CURSOR
	
	SET @InvoiceList = CURSOR STATIC FOR 
	SELECT  lngInvoiceID 
	FROM manufacturing.tblInvoiceOrderDetails
	WHERE lngOrderDetailID = @OdID;
		  
	OPEN @InvoiceList

	SET @InvoiceCount= @@CURSOR_ROWS
	IF @InvoiceCount=0 
	BEGIN	
		SET @PnumList=''
		SET @InvoiceIDs=''
	END
	ELSE
	BEGIN
		IF @InvoiceCount=1 
		BEGIN
			FETCH NEXT FROM @InvoiceList INTO @InvoiceIDs
		END
		ELSE --InvoiceCount > 1
		BEGIN
			FETCH NEXT FROM @InvoiceList INTO @InvoiceID
	
			WHILE @@Fetch_Status=0 
				BEGIN
				SET @InvoiceIDs=convert(varchar(1024),isnull(@InvoiceIDs,'')) + ',' + convert(varchar(10),@InvoiceID)
				FETCH NEXT FROM @InvoiceList INTO @InvoiceID
			END
		END
	END
	
	CLOSE @InvoiceList
	DEALLOCATE @InvoiceList

	IF Len(@InvoiceIDs) > 0 
	BEGIN
	
		SET @InvoiceIDs = RIGHT(@InvoiceIDs, Len(@InvoiceIDs) - 1)
	
		DECLARE @PNUMLISTSET CURSOR
		
		SET @PNumListSet=CURSOR STATIC FOR 
		Select strPNumber 
		from finance.tblInvoices 
		where lngInvoiceID in (@InvoiceIDs) --This is the line that generates the type conversion error
		and strPNumber is not null
						   
		OPEN @PNumListSet

		SET @PNUMCOUNT=@@CURSOR_ROWS
	
		IF @PNUMCOUNT=0
			SET @PnumList=''
		ELSE
			BEGIN
			IF @PNUMCOUNT=1
				BEGIN	
					FETCH NEXT FROM @PNUMLISTSET INTO @PnumList
				END	
			ELSE
				BEGIN
					FETCH NEXT FROM @PNUMLISTSET INTO @PNUM
					WHILE @@Fetch_Status=0 
						BEGIN
							SET @PnumList=convert(varchar(1024),isnull(@PnumList,'')) + ',' + convert(varchar(1024),@PNUM)
							FETCH NEXT FROM @PNUMLISTSET INTO @PNUM
						END
						SET @PnumList = RIGHT(@PnumList, Len(@PnumList) - 1)
				END
			END
	CLOSE @PNumListSet
	DEALLOCATE @PNumListSet

	END

	RETURN nullif(@PnumList,'')
END
	

ALTER FUNCTION [dbo].[pnumsforodid]( @OdID INT ) 
RETURNS VARCHAR(1024)
AS
BEGIN
        DECLARE @PnumList varchar(1024)

	SELECT  @PnumList = isnull(@PnumList + ',', '') + i.strPNumber
	FROM 	manufacturing.tblInvoiceOrderDetails iod
		inner join finance.tblInvoices i	on	iod.lngInvoiceID	= i.lngInvoiceID
	WHERE 	iod.lngOrderDetailID = @OdID
        GROUP BY i.strPNumber;

	RETURN @PnumList
END

quote:

---

Originally posted by khtan

You don't need cursor to do that.

ALTER FUNCTION [dbo].[pnumsforodid]( @OdID INT ) 
RETURNS VARCHAR(1024)
AS
BEGIN
        DECLARE @PnumList varchar(1024)

<snip>....</snip>

	RETURN @PnumList
END

---

	SELECT  i.strPNumber
	FROM 	manufacturing.tblInvoiceOrderDetails iod
		inner join finance.tblInvoices i	on	iod.lngInvoiceID	= i.lngInvoiceID
	WHERE 	iod.lngOrderDetailID = @OdID
        GROUP BY i.strPNumber;

@PnumList = isnull(@PnumList + ',', '') + i.strPNumber