sql-servert-sqlvarchar

Varying SQL query results

I have a table valued function called "elara_customer_byname". When I execute SELECT * FROM elara_customer_byname('%Kar%') I get 2 rows, which is incorrect. If I copy the SQL from the function and put it in the query window passing the same parameter I get a single row (correct). Cannot figure out what's going on

Table valued Function

USE [Elara] GO 
/****** Object: UserDefinedFunction [dbo].[elara_customer_byname] 
Script Date: 11/3/2025 11:48:53 AM ******/ 

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER FUNCTION [dbo].[elara_customer_byname] (@pName varchar ) 
RETURNS TABLE AS RETURN ( 
SELECT ID, parentid, displaytext FROM Customer WHERE CustomerName LIKE @pName )

Calling function from Query window

select * from elara_customer_byname('%kar%')

Executing SQL text directly from query window

SELECT ID, parentid, displaytext 
FROM Customer 
WHERE CustomerName LIKE '%kar%'

enter image description here

Solution

  • ALTER FUNCTION [dbo].[elara_customer_byname] ( @pName varchar )

    Therefore:

    select * from elara_customer_byname('%kar%')
=>
select * from elara_customer_byname('%')

    Parameter should have length defined, otherwise the default is varchar(1):

    ALTER FUNCTION [dbo].[elara_customer_byname] ( @pName varchar(1000) )

    db<>fiddle demo