SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scalar function syntax error


Scalar function syntax error

Author
Message
saintor1
saintor1
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 102
I have two functions that work. "A" and "B" When I tried to modify "A" to have condtiions like "B" (now "C") it doesn't work.

"A"

USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl] Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[QtyOnHandNoExcl](@SC nVarChar(30), @Wh nVarchar(3))
RETURNS Decimal(8,3)
AS
-- Returns the on hand without bins starting by MB
BEGIN
DECLARE @ret Decimal(8,3);
SELECT @ret = SUM(QtyOnHand1)
FROM SysproSStar.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%';
RETURN @ret;
END;


"B"

USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[LastBuyer] Script Date: 2017-10-12 09:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[LastBuyer]
(@SC1 varchar(30), @CIE varchar(1))
RETURNS VARCHAR(3)
AS
BEGIN
DECLARE @LB VARCHAR(3)
SELECT @LB = CASE @CIE WHEN 'Q' THEN

(SELECT TOP 1 Buyer
FROM vPurch_Q
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC)
WHEN 'I' THEN
(SELECT TOP 1 Buyer
FROM vPurch_I
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC)
WHEN 'K' THEN
(SELECT TOP 1 Buyer
FROM vPurch_K
WHERE MStockCode=@SC1
ORDER BY OrderEntryDate DESC)

END;

RETURN(@LB);
END


Now the one that doesn't work

"C"

USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl] Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[QtyOnHandNoExcl]
(@SC nVarChar(30), @Wh nVarchar(3), @CIE varchar(1))
RETURNS Decimal(8,3)
AS
BEGIN
DECLARE @ret Decimal(8,3)
SELECT @ret = CASE @CIE
WHEN 'K' THEN
(SELECT @ret = SUM(QtyOnHand1)
FROM SysproSStar.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%')
WHEN 'N' THEN
(SELECT @ret = SUM(QtyOnHand1)
FROM SysproSStarNZ.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%')
WHEN 'O' THEN
(SELECT @ret = SUM(QtyOnHand1)
FROM SysproSStarAust.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%')
END;
RETURN(@ret);
END;


I get
Msg 102, Niveau 15, État 1, Procédure QtyOnHandNoExcl, Ligne 9 [Ligne de départ du lot 7]
Incorrect syntax near '='.
Msg 156, Niveau 15, État 1, Procédure QtyOnHandNoExcl, Ligne 20 [Ligne de départ du lot 7]
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Niveau 15, État 1, Procédure QtyOnHandNoExcl, Ligne 27 [Ligne de départ du lot 7]
Incorrect syntax near the keyword 'RETURN'.


Any assistance would be appreciated,.
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73281 Visits: 20499
I see what the error is, but I'm not sure what you want to do. Both functions seem unrelated, so having the same structure is illogical.
Also, check the following article: How to Make Scalar UDFs Run Faster (SQL Spackle) - SQLServerCentral


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Thom A
Thom A
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33854 Visits: 11673
You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

(Also, yes, see Luis' link).


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
saintor1
saintor1
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 102
Thom A - Thursday, October 12, 2017 8:43 AM
You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

(Also, yes, see Luis' link).

New "C" (which works). Awesome. Many thanks.

USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl] Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[QtyOnHandNoExcl]
(@SC nVarChar(30), @Wh nVarchar(3), @CIE varchar(1))
RETURNS Decimal(8,3)
AS
BEGIN
DECLARE @ret Decimal(8,3)
SELECT @ret = CASE @CIE
WHEN 'K' THEN
(SELECT SUM(QtyOnHand1)
FROM SysproSStar.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%')
WHEN 'N' THEN
(SELECT SUM(QtyOnHand1)
FROM SysproSStarNZ.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%')
WHEN 'O' THEN
(SELECT SUM(QtyOnHand1)
FROM SysproSStarAust.dbo.InvMultBin p
WHERE p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%')
END;
RETURN(@ret);
END;

Thom A
Thom A
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33854 Visits: 11673
saintor1 - Thursday, October 12, 2017 9:01 AM
Thom A - Thursday, October 12, 2017 8:43 AM
You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

(Also, yes, see Luis' link).

New "C" (which works). Awesome. Many thanks.

Great, but did you look at Luis' link?



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Steve Jones
Steve Jones
SSC Guru
SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)

Group: Administrators
Points: 249964 Visits: 19813
And be super careful of these scalar functions. They often cause performance issues.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73281 Visits: 20499
saintor1 - Thursday, October 12, 2017 9:01 AM
Thom A - Thursday, October 12, 2017 8:43 AM
You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

(Also, yes, see Luis' link).

New "C" (which works). Awesome. Many thanks.

And this is why I didn't want to explain the problem. You are repeating your code 3 times. There's no use for that.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
sgmunson
sgmunson
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32542 Visits: 5185
If you decide you need an inline table-valued function for performance reasons, try this:
USE [W_SE-MFG]
GO
/****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl] Script Date: 2017-10-12 09:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION dbo.QtyOnHandNoExcl (
@SC nvarchar(30),
@Wh nvarchar(3),
@CIE varchar(1)
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
SELECT CONVERT(decimal(8,3), SUM(X.QtyOnHand1)) AS RET
FROM (
SELECT 'K' AS CIE, QtyOnHand1
FROM SysproSStar.dbo.InvMultBin AS p
WHERE @CIE = 'K'
AND p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%'
UNION ALL
SELECT 'N', QtyOnHand1
FROM SysproSStarNZ.dbo.InvMultBin AS p
WHERE @CIE = 'N'
AND p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%'
UNION ALL
SELECT 'O', QtyOnHand1
FROM SysproSStarAust.dbo.InvMultBin AS p
WHERE @CIE = 'K'
AND p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%'
) AS X;
GO



Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
saintor1
saintor1
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 102
I guess that I did something that should be a no-no... lol! I remember having seen this RETURN TABLE instead of a variable in a SQLCENTRAL article a few months ago, I was interested but honestly I didn't understand some crucial points and didn't push the exercise. Baby steps for me.

Steve I tried your code and I got

Msg 2010, Niveau 16, État 1, Procédure QtyOnHandNoExcl, Ligne 8 [Ligne de départ du lot 7]
Cannot perform alter on 'dbo.QtyOnHandNoExcl' because it is an incompatible object type.


If I get this right, your approach implies an union of the 3 tables, so 200000 rows instead of 40000-70000 and it still will work faster?
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73281 Visits: 20499
saintor1 - Thursday, October 12, 2017 1:07 PM
I guess that I did something that should be a no-no... lol! I remember having seen this RETURN TABLE instead of a variable in a SQLCENTRAL article a few months ago, I was interested but honestly I didn't understand some crucial points and didn't push the exercise. Baby steps for me.

Steve I tried your code and I got

Msg 2010, Niveau 16, État 1, Procédure QtyOnHandNoExcl, Ligne 8 [Ligne de départ du lot 7]
Cannot perform alter on 'dbo.QtyOnHandNoExcl' because it is an incompatible object type.


If I get this right, your approach implies an union of the 3 tables, so 200000 rows instead of 40000-70000 and it still will work faster?


You would need to do a DROP and CREATE because you had a scalar function and this is an inline table-valued function.
The approach shows the UNION ALL among the 3 tables, but would only read one (or none) depending on the parameter as 2 of the queries will have conditions that return false without having to read the table.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search