Why does setting a varible like this make a difference?

Question

I have a scalar value function that returns a VarChar(MAX) In my stored procedure I do this

declare @p_emailAddr varchar(MAX) = (select db.dbo.GetEmails(10))

If I do print @p_emailAddr it shows me it was populated with the correct information but the rest of the code doesn't work correctly using it. (I have no clue why, it doesn't make sense!)

Now if I change it like this

declare @p_emailAddr varchar(MAX) = 'test@email.com;'

The rest of my code works perfect as it should!

What is the difference between the two methods of setting @p_emailAddr that is breaking it?

This is get emails code

ALTER FUNCTION [dbo].[GetEmails](@p_SubID int)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @p_Emails varchar(max)

    SELECT @p_Emails = COALESCE(@p_Emails + ';', '') + E.EmailAddress
    FROM    db.dbo.UserEmailAddr E JOIN
            db.dbo.EmailSubscriptionUsers S on e.ClockNumber = s.Clock AND S.SubID = @p_SubID

    SET @p_Emails = @p_Emails + ';'
    RETURN @p_Emails
END

It doesn't make any difference. The values returned by the function vs that in the string literal can't in fact be the same. — Martin Smith, Dec 11 '15 at 14:43
I added the code for get emails. And what I meant by "Doesn't work correctly" as in it doesn't execute using @p_emailAddr set using the function. — Timmy, Dec 11 '15 at 14:48
Does your code works for `declare @p_emailAddr varchar(MAX) = 'test@email.com;test2@email.com;'` Show the rest of your code your function may return more than one email(anyway it highly depend on underlying table, you should use FOR XML + STUFF instead) — Lukasz Szozda, Dec 11 '15 at 14:48
What is the result of `select db.dbo.GetEmails(10)`? (NULL/partial text) — Lukasz Szozda, Dec 11 '15 at 14:50
The results of `select db.dbo.GetEmails(10)` is one big string of emails. Separated by semicolons. — Timmy, Dec 11 '15 at 14:52
Yes, they are correct. That's why I'm stumped on why it would make any difference since I would be setting a variable that is `varchar(max)` to the function that returns `varchar(max)` — Timmy, Dec 11 '15 at 14:54
I suspect http://stackoverflow.com/questions/32806457/strange-tsql-behavior-with-coalesce-when-using-order-by/32806605#32806605 — Lukasz Szozda, Dec 11 '15 at 14:57
@lad2025 Nope, no luck on that. I was really hoping that would work. — Timmy, Dec 11 '15 at 14:57
@lad2025 Just wanted to update you, it turned out the database I was declaring the variable in didn't have permission to the database `GetEmails` was in. So it was just failing at that point. I've implemented error handling through SQL now so if anything like that happens again I'll receive it app side. The logic of the stored procedure was good from the start! Life lesson learned! — Timmy, Dec 11 '15 at 18:54

score 0 · Answer 1 · answered Dec 11 '15 at 19:29

What's coming back from GetEmails(10)? varchar(max) is a string value and is expecting a single value. you could have a table variable or if dbo.getemails(10) is a table just join it where you're expecting to use @p_emailaddr

best

select *
from table1 t1
join dbo.GetEmails(10) e
on e.email = t1.email

alternative

create table #GetEmails (emails varchar(max))
insert into #GetEmails values ('email@test.com'), ('test@email.com')

declare @p_emailAddr table (emails varchar(max))
insert into @p_emailAddr(emails)
select * 
from #GetEmails


select * 
from @p_emailAddr

The problem was found. Check my last comment in OP, it was permission issues between the stored procedure and the getEmails() functions. — Timmy, Dec 11 '15 at 19:32

Why does setting a varible like this make a difference?

1 Answers1