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)
 Custom Function

Author  Topic 

Timax
Starting Member

37 Posts

Posted - 2015-03-31 : 04:26:59
Please help me to understand why my function doesn't work:
ALTER Function [dbo].[CLeadTime]
(
@PN AS VarChar
)
RETURNS INT

AS
BEGIN

DECLARE @PNLT Varchar, @FPNLT Varchar

Set @PNLT = @PN
SELECT @FPNLT = [MaxOfLead Time] FROM dbo.CompLeadTime WHERE [Part Number] = @PNLT
RETURN @FPNLT

END

What am I doing wrong?

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 05:55:47
First of all, what you mean by "doesn't work" ?

When you execute , it is running but not value it is return ?
Maybe no records much to the clause
Where [Part Number] = @PNLT

Maybe you need to increase all the variables to VARCHAR(500)

Can you post some samples of data from table and the call of the function ?


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 05:58:56
Another remark:
when you not specify the size of your VARCHAR , the default is 1. So your value is cut to a single character.


sabinWeb MCP
Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-03-31 : 06:45:50
quote:
Originally posted by stepson

Another remark:
when you not specify the size of your VARCHAR , the default is 1. So your value is cut to a single character.


sabinWeb MCP


Thank you very much!!! That was my problem, I did not specify VARCHAR size... Appreciate your help!!!
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-31 : 09:57:36
For efficiency, you should get rid of the local variables as well.

ALTER Function [dbo].[CLeadTime]
(
@PN AS VarChar(30)
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT [MaxOfLead Time]
FROM dbo.CompLeadTime
WHERE [Part Number] = @PN
)
END

Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-04-01 : 06:37:11
I used local variables in this statement for further development of the custom function. I need to use IF or CASE SELECT in it. By the way, what is the most efficient out of two?
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-01 : 11:01:01
You almost certainly don't need local variables, even with a CASE statement.

Most efficient is a single RETURN() statement in the function, with subqueries / derived tables as needed but no local variables.
Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-04-01 : 17:18:23
Hmm... how can I do this without local var?

ALTER Function [dbo].[CLeadTime]
(
@PN AS VarChar(20)
)
RETURNS INT

AS
BEGIN

DECLARE @FPNLT Varchar(10)


SET @FPNLT = (SELECT MAX(dbo.CompAvailability.[Lead Time]) AS [MaxOfLead Time]
FROM dbo.ABC_PN LEFT OUTER JOIN
dbo.CompTransactions ON dbo.ABC_PN.[Part Number] = dbo.CompTransactions.[Part Number] LEFT OUTER JOIN
dbo.CompAvailability ON dbo.CompTransactions.[#] = dbo.CompAvailability.CompReq#
GROUP BY dbo.ABC_PN.[Part Number]
HAVING (NOT (MAX(dbo.CompAvailability.[Lead Time]) IS NULL)) AND (dbo.ABC_PN.[Part Number] = @PN))

IF @FPNLT IS NULL
SET @FPNLT = 'Unknown'

RETURN @FPNLT

END
Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-04-01 : 17:21:09
By the way, This IF statement don't work because it looks for INT although I specified VARCHAR
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 20:23:09
This perhaps?

RETURN COALESCE(
(
SELECT MAX(dbo.CompAvailability.[Lead Time]) AS [MaxOfLead Time]
....
), 'Unknown')


"By the way, This IF statement don't work because it looks for INT although I specified VARCHAR"

Assuming that dbo.CompAvailability.[Lead Time] is INT ?? then you would need

RETURN COALESCE(
(
SELECT CONVERT(varchar(10), MAX(dbo.CompAvailability.[Lead Time])) AS [MaxOfLead Time]
....
), 'Unknown')

Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-04-02 : 04:44:58
Works! but... it slower than before. Twice. Not sure if it's VARCHAR conversion that makes a difference or the COALESCE function that takes time. I kept previous version that have If statement just changed RETURNS from INT to VARCHAR and it worked fine.
Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-04-02 : 05:06:55
I have another custom function that I am moving from msaccess to mssql which need to return multiple fields from select statement like SELECT Field1. Field2 and so on. How do I handle that? Do I need Table valued function for that? Create @table and record things there to have multiple fields from Select statement?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-02 : 09:26:39
By the by, when functions first came out we thought they were great for centralising snippets of code etc. We then had an incident where a function was causing a query to recompile. That query was used thousands of times a second, on a server concurently accessed by thousands of customers. It crippled the performance ...

We now give most UDFs a wide berth!

I'm guessing but maybe this will perform better / differently

RETURN COALESCE(
(
SELECT CONVERT(varchar(10),
(
SELECT MAX(dbo.CompAvailability.[Lead Time])
....
)
), 'Unknown')
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-02 : 10:26:22
[code]

ALTER Function [dbo].[CLeadTime]
(
@PN AS VarChar(20)
)
RETURNS INT

AS
BEGIN
RETURN
ISNULL(
CAST((
SELECT MAX(dbo.CompAvailability.[Lead Time]) AS [MaxOfLead Time]
FROM dbo.ABC_PN LEFT OUTER JOIN
dbo.CompTransactions ON dbo.ABC_PN.[Part Number] = dbo.CompTransactions.[Part Number] LEFT OUTER JOIN
dbo.CompAvailability ON dbo.CompTransactions.[#] = dbo.CompAvailability.CompReq#
WHERE dbo.ABC_PN.[Part Number] = @PN
GROUP BY dbo.ABC_PN.[Part Number]
--HAVING ...: not needed, because MAX will ignore NULL values anyway
) AS varchar(20)),
'Unknown'
)
END

[/code]
Go to Top of Page

Timax
Starting Member

37 Posts

Posted - 2015-04-02 : 21:16:23
Nicely done! Works much faster with CAST function. Thank you very much!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-07 : 07:49:02
quote:
Originally posted by Timax

Works much faster with CAST function.


... or adding the WHERE clause? Or did you literally just change CONVERT to CAST?
Go to Top of Page
   

- Advertisement -