Selecting a user-defined scalar function that takes as a parameter another field

Question

I have a table a with a list of id's, and a user-defined function foo(id) that takes the id and returns a VARCHAR(20).

What I am trying to do is:

SELECT 
id,
foo(id) AS 'text field'
FROM a

However, instead of calling the function for each ID number, like I desired, the text comes back the same for every row. I have tested the foo() function manually with the returned ID's and it does not have that problem, so I realize I must not understand something about the evaluation of the query.

Answer 1

This worked for me. I'm not sure what your saying you get.

CREATE TABLE [dbo].[a](
 [id] [int] NULL
) 

insert into a select 1
insert into a select 2

insert into a select 4
insert into a select 5

CREATE FUNCTION foo(@id int) RETURNS varchar(20)
AS
BEGIN
 DECLARE @ResultVar varchar(20)
 SELECT @ResultVar = '# - ' + CAST(@id as varchar(20))
 RETURN @ResultVar
END

select id, dbo.foo(id) AS 'text field' from a

returns

id          text field
----------- --------------------
1           # - 1
2           # - 2
4           # - 4
5           # - 5
6           # - 6

Answer 2

If the output of the function is functionally dependent on the input, then it will be called for each row, as you expect. If you see a different result, it means that you do not pass the correct input or your function output is not dependent on its input.