Multiple Parameters in UDF in SQL

Question

I am trying to established a Function to have multiple parameters as see below @SITENAME, @MARKET and @DATE.

ALTER FUNCTION [dbo].[Platform_Parser]
(
  @SITENAME NVARCHAR(2000) NULL
  ,@MARKET NVARCHAR(2000) NULL
  ,@DATE Date NULL
)
RETURNS NVARCHAR(2000)
AS
BEGIN
    RETURN
  CASE
WHEN @SITENAME LIKE '%GOOGLE%' AND @DATE < '2022-01-01' AND @MARKET IN ('FR','DE') THEN 'TWITTER'
    WHEN @SITENAME LIKE '%GOOGLE%' AND @DATE < '2022-01-01' AND @MARKET = 'UK' THEN 'YOUTUBE'
    ELSE 'Unclassified'
  END
END
GO

This is the data in [dbo].[MyTable]

SiteName	Market	Date
GOOGLE	FR	11/11/2021
YAHOO	FR	09/05/2021
GOOGLE	DE	03/07/2021
GOOGLE	UK	05/12/2021
GOOGLE	UK	05/05/2022
YAHOO	DE	03/02/2022
YAHOO	UK	21/03/2022

However, when I try to implement the function in a different view as below I experience the error that I have too many arguments.

SELECT 

[dbo].[Platform_Parser]([SiteName],[Market],[Date])   AS [Publisher]
,[Market]  AS [Market]
,[Date] AS [Date]

FROM [dbo].[MyTable]

Result should be as below

Publisher	Market	Date
TWITTER	FR	11/11/2021
YAHOO	FR	09/05/2021
TWITTER	DE	03/07/2021
YOUTUBE	UK	05/12/2021
GOOGLE	UK	05/05/2022
YAHOO	DE	03/02/2022
YAHOO	UK	21/03/2022

If you aren't using SQL Server 2019, I would suggest against a multi-line scalar function anyway. As for Table Type parameters, you can use these against functions, but I doubt it's the way you want to; you don't pass a table name to it, you would need to define a variable of the appropriate user defined table TYPE in INSERT the rows into it. What are you actually trying to achieve here; i suspect what you have are asking here is an XY Problem. — Thom A
– Thom A ♦, Commented Jul 26, 2022 at 13:27
Thanks @Larnu, initially I had a working Function that determined the Platform with a singular parameter of "@SITENAME" however then had to further parameters to determine these Platforms using "Date" and "Market" as well as SITENAME. That is my main problem — James
– James, Commented Jul 26, 2022 at 15:34
That doesn't really explain the actual problem you want to solve. Especially when you want to pass a table type parameter to the function, yet the function doesn't even have a FROM, so having a table type doesn't fit with the current design. You need to explain in detail, the problem you are trying to solve. Provide sample data (in a consumable format) and expected results. — Thom A
– Thom A ♦, Commented Jul 26, 2022 at 15:36
Sorry @Larnu, first post on Stack, I have updated the initial post hopefully to simplify the problem. — James
– James, Commented Jul 26, 2022 at 15:49
I can't replicate your problem. db<>fiddle. Can you provide an minimal reproducible example? — Thom A
– Thom A ♦, Commented Jul 26, 2022 at 16:09

Jeremy Caney · Accepted Answer · 2022-07-28 01:35:38Z

-1

You can use your function in queries with different tables, you don't need to include the table name in the function.

For your example, you can write something like this:

select [dbo].[Platform_Parser]([SiteName],[Market],[Date]) 
from [dbo].[ThisTable]

Just make sure [SiteName], [Market], and [Date] are columns in [dbo].[ThisTable]; or, if not, pass the column names as the function parameters.

edited Jul 28, 2022 at 1:35

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Jul 26, 2022 at 15:33

George

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple Parameters in UDF in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related