SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with last hurdle of my UDF please?!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andrewmrichards
Starting Member

United Kingdom
3 Posts

Posted - 11/27/2012 :  19:02:09  Show Profile  Reply with Quote
Hi All

I'm trying to create a UDF, and my TSQL skills when it comes to Cursors is rustier that I'd like!

The idea of the function is essentially this:

1. A supplied parameter of @ODID identifies an Order Detail ID. This is used to look up from 0 - n matching InvoiceIDs. These are retrieved by means of a cursor, which may return 0 rows (@InvoiceIDs is populated with an empty string), 1 row (@InvoiceIDs is populated with that one InvoiceID) or more than one row (@InvoiceIDs is populated with a comma-delimited set of InvoiceID values).

2. A second cursor is created to look up a "P number" (a reference to a paper file used by my client), incorporating the WHERE clause ' WHERE lngInvoiceID IN (@InvoiceIDs)'.

At least that's the theory. But, if there are no matching invoices, I get the null return that I expect. If there is one matching invoice (and P number) I still get Null. And if there are multiple matching invoices (and P Numbers) then I get the error "Conversion failed when converting the varchar value '5366,5829,6442' to data type int." (These are the Invoices that match the Order Detail ID I supplied in this case).

I hope that makes sense - I'm pasting the entire UDF below in case that helps more. I'd be really grateful for any help anyone can offer me...

Thanks a lot
Andrew


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
	

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/27/2012 :  21:18:27  Show Profile  Reply with Quote
You don't need cursor to do that.

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



KH
Time is always against us


Edited by - khtan on 11/27/2012 21:19:45
Go to Top of Page

andrewmrichards
Starting Member

United Kingdom
3 Posts

Posted - 11/28/2012 :  03:20:40  Show Profile  Reply with Quote
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





Brilliant - thanks for that. That's exactly what I needed and is SO much more elegant than what I was attempting to write!!!

If you (or anyone else) could do me a favour, I'd be really grateful - could you explain exactly how this is working? I'm struggling to understand how the inherent loop in this statement works to return every P Number....

Thanks again - you've really helped.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/28/2012 :  03:39:13  Show Profile  Reply with Quote
this query returns the unique strPNumber in a column

	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;


To convert it to a CSV, append it to a string variable @PnumList

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


as the @PnumList variable is NULL initially, the ISNULL() checks for that and return an empty string for the very first time.


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/28/2012 :  03:40:43  Show Profile  Reply with Quote
You might also want to take a look at these 2 link.
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

The 2nd link shows you how to get the CSV without using a UDF


KH
Time is always against us

Go to Top of Page

andrewmrichards
Starting Member

United Kingdom
3 Posts

Posted - 11/28/2012 :  04:05:22  Show Profile  Reply with Quote
Thank you so much - that's really helpful.

Andrew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000