Member Avatar for Tank50

I use SQL Server 2005 and I create function.Below I mention the Function that I created.

CREATE FUNCTION dbo.udf_GetCountryID(@Name varchar(50))
RETURNS VARCHAR(1000) AS

BEGIN
Declare @Description varchar(4000)
select @Description = coalesce(@Description + ',' , '' ) +  countryCode 
FROM dbo.Test_Table where CommonName='Japan'

Return @Description
END

After creating function I use below SQL statement

select dbo.udf_GetCountryID(Distinct(F.CommonName))CountryName from dbo.Test_Table F

but I got the Error message.Below I mention the error message also
Msg 208, Level 16, State 101, Line 1
Invalid object name 'dbo.udf_GetCountryID'.


Once I create the function its comes under scalar-valued function.I am using sql server 2005.Please help me

Thanks
Tank50

Edited by Tank50 because: n/a
  • 2 Contributors
  • 2 Replies
  • 231 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by Tank50

Dani AI

Generated

Good catch by — the syntax problem was the way DISTINCT was used when calling a scalar UDF. Applying DISTINCT to the function’s returned value (rather than trying to put DISTINCT inside the function argument) fixes the immediate error. Beyond that, there are a few practical improvements and pitfalls worth noting so this stays robust long-term.

Make sure the function actually uses its parameter (the original code declared one but used a hard-coded name). Align the declared return type with the internal buffer to avoid silent truncation (use NVARCHAR/VARCHAR(MAX) when concatenated results can be large). The SELECT-variable concatenation trick works, but the row order is undefined unless you force an ORDER BY. Also be aware scalar UDFs run per row and can be very slow in set-based queries — prefer set-based aggregation or an inline table-valued function where possible.

For SQL Server 2005 a safer, set-based pattern is FOR XML PATH plus STUFF (this avoids the undocumented row-assignment behavior and lets you control order). Example (adapt and test in your environment):

CREATE FUNCTION dbo.udf_GetCountryCodes(@Name NVARCHAR(100))
RETURNS NVARCHAR(4000)
AS
BEGIN
  DECLARE @Result NVARCHAR(4000);

  SELECT @Result = STUFF((
    SELECT ',' + t.countryCode
    FROM dbo.Test_Table t
    WHERE t.CommonName = @Name
    ORDER BY t.countryCode        -- optional, makes order predictable
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

  RETURN @Result;
END;

If you are on SQL Server 2017 or later, prefer the built-in STRING_AGG for simpler, faster aggregation. Finally, confirm you create and call the function in the same database/schema (use the dbo prefix as you did) and test with various inputs to check truncation, ordering, and performance.

Recommended Answers

All 2 Replies

Member Avatar for MeSampath

Hi,

Could you give a try with following code change;

SELECT DISTINCT(dbo.udf_GetCountryID(F.CommonName)) AS CountryName 
FROM dbo.Test_Table F

Good luck.

Member Avatar for Tank50

Thanks MeSampath.Its works.Thank you

Tank50

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.