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

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

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

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!!!

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

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?

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.

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

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

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')

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.

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?

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')

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]

Timax
Starting Member

37 Posts

Posted - 2015-04-02 : 21:16:23

Nicely done! Works much faster with CAST function. Thank you very much!

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources