UDF does not return value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UDF does not return value

Author

Topic

joeller
Starting Member

15 Posts

Posted - 2014-08-12 : 11:03:13

HI all;
I have an User-defined scalar function. It runs a query to populate a variabale and it returns the variable. I checked the query in the UDF seaparately and it works just fine. However when I call the UDF and pass in my parameters, the return is null.
Does anyone have any idea?
Here is the stored procedure:

ALTER FUNCTION [dbo].[zt_prc_fnNSNsFmNIIN_Get]  
(
	@NIIN varchar(5),
	@PN varchar(20)
)
RETURNS Varchar(50)
AS
BEGIN
	-- Declare the return variable here
	Declare @NSN varchar(50)
	Set @NSN = (SELECT Convert(varchar(20), RTRIM(NSN.FSC)) + Convert(varchar(20), RTrim(NSN.NIIN)) NSN
				FROM TechManual.dbo.ANSNS NSN 
					JOIN TechManual.dbo.AITEMTABLE AIT ON NSN.PRIMARYPARTNUMBER = AIT.PRIMARYPARTNUMBER and NSN.DODIC = AIT.DODIC
					JOIN TechManual.dbo.PARTNUMBERS PN ON AIT.PRIMARYPARTNUMBER = PN.PRIMARYPARTNUMBER
				WHERE NSN.NIIN = @NIIN AND PN.PARTNUMBER = @PN)
	RETURN @NSN

END

Here is my usage of it as well as a check of the query.

Select @NIIN
Select dbo.zt_prc_fnNSNsFmNIIN_Get('015132378', '850AS911')

	Set @NSN = (SELECT Convert(varchar(20), RTRIM(NSN.FSC)) + Convert(varchar(20), RTrim(NSN.NIIN)) NSN
				FROM TechManual.dbo.ANSNS NSN 
					JOIN TechManual.dbo.AITEMTABLE AIT ON NSN.PRIMARYPARTNUMBER = AIT.PRIMARYPARTNUMBER and NSN.DODIC = AIT.DODIC
					JOIN TechManual.dbo.PARTNUMBERS PN ON AIT.PRIMARYPARTNUMBER = PN.PRIMARYPARTNUMBER
				WHERE NSN.NIIN = '015132378' AND PN.PARTNUMBER = '850AS911')
	Select @NSN

The Check code query returns a value of 1377015132378 for @NSN. However the function call returns NULL. No idea why?

E.R. Joell MCDBA

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-12 : 11:12:31

@NIIN is varchar(5) but you are passing in a 9 digit value. The value gets truncated, the results yield nothing and the @NSN variable returns its uninitialized value of Null. Et voila!
(FWIW, we've ALL been there!)

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources