Varying SQl query results [duplicate]

Question

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

sqlblog.org/2009/10/09/…

Aaron Bertrand
– Aaron Bertrand

2025-11-03 18:58:48 +00:00
Commented Nov 3 at 18:58 — Aaron Bertrand
– Aaron Bertrand, Commented Nov 3 at 18:58

Lukasz Szozda · Accepted Answer · 2025-11-03 17:23:35Z

4

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

edited Nov 3 at 17:23

answered Nov 3 at 17:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

Varying SQl query results [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related