Dynamic SQL calling a function

I have a stored procedure that builds a dynamic SQL string in which an
inline User Defined Function is called. Once the string is build I execute
using sp_executesql. The select runs fine except that no values are being
returned from the inline function. When I run the resulting SQL string in
Query Analyzer it runs as expected.
The function is working properly.
I tried using Exec @.SQL instead of Exec sp_ExecuteSql @.Sql but the results
are the same.
I know I must be missing something but I can’t find a thing online about
this issue.
Thanks so much for the help!
RJ
Here is the Select Code
set @.SQL = 'SELECT dbo.TB_Events.EV_EventId, dbo.TB_Events.EV_AccountId,
dbo.TB_Events.EV_EventName, dbo.TB_Accounts.MA_AccountName,
dbo.TB_Accounts.MA_AccountManager,
dbo.TB_Events.EV_ContactId,
dbo.fn_ContactName(dbo.TB_Events.EV_ContactId,1) as MainContact,
dbo.TB_Events.EV_AltContactId,dbo.fn_ContactName(d bo.TB_Events.EV_AltContactId,1) as AltContact,
dbo.TB_Events.EV_OnSiteContactId,
dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactI d,1) as OnSiteContact,
dbo.TB_Events.EV_EventType, dbo.TB_Events.EV_PostAs,
dbo.TB_Events.EV_StartDate, dbo.TB_Events.EV_EndDate,
dbo.TB_Events.EV_Status,
dbo.TB_Events.EV_StatusDate,
dbo.TB_Events.EV_ProspectDate, dbo.TB_Events.EV_TentativeDate,
dbo.TB_Events.EV_DefiniteDate,
dbo.TB_Events.EV_HistoricDate,
dbo.TB_Events.EV_CancelDate, dbo.TB_Events.EV_ProposalCreateDate,
dbo.TB_Events.EV_ContractCreateDate,
dbo.TB_Events.EV_CutoffDate,
dbo.TB_Events.EV_EventSummary, dbo.TB_Events.EV_BookingSource,
dbo.TB_Events.EV_EventProfile,
dbo.TB_Events.EV_EventFrequency,
dbo.TB_Events.EV_BookingLead, dbo.TB_Events.EV_ReservationMethod,
dbo.TB_Events.EV_BookingCode,
dbo.TB_Events.EV_SpecialRequests,
dbo.TB_Events.EF_MarketSegment
FROM dbo.TB_Events INNER JOIN
dbo.TB_Accounts ON dbo.TB_Events.EV_AccountId =
dbo.TB_Accounts.MA_AccountId'
Hi RJ
There are special issues with getting output values back through
sp_executesql. This KB article explains how to use output parameters with a
stored procedure. I haven't tried using it with functions, but since you
just want a value returned, you could turn your function into a procedure
and have the return value be an output parameter.
"How to specify output parameters when you use the sp_executesql stored
procedure in SQL Server"
http://support.microsoft.com/kb/262499/en-us
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"RJ" <RJ@.discussions.microsoft.com> wrote in message
news:0A7BAFB6-5E44-4ED0-96E6-892701C44CBD@.microsoft.com...
>
> I have a stored procedure that builds a dynamic SQL string in which an
> inline User Defined Function is called. Once the string is build I
> execute
> using sp_executesql. The select runs fine except that no values are being
> returned from the inline function. When I run the resulting SQL string in
> Query Analyzer it runs as expected.
> The function is working properly.
>
> I tried using Exec @.SQL instead of Exec sp_ExecuteSql @.Sql but the results
> are the same.
> I know I must be missing something but I can't find a thing online about
> this issue.
> Thanks so much for the help!
> RJ
>
> Here is the Select Code
> set @.SQL = 'SELECT dbo.TB_Events.EV_EventId,
> dbo.TB_Events.EV_AccountId,
> dbo.TB_Events.EV_EventName, dbo.TB_Accounts.MA_AccountName,
> dbo.TB_Accounts.MA_AccountManager,
> dbo.TB_Events.EV_ContactId,
> dbo.fn_ContactName(dbo.TB_Events.EV_ContactId,1) as MainContact,
> dbo.TB_Events.EV_AltContactId,dbo.fn_ContactName(d bo.TB_Events.EV_AltContactId,1)
> as AltContact,
> dbo.TB_Events.EV_OnSiteContactId,
> dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactI d,1) as OnSiteContact,
> dbo.TB_Events.EV_EventType, dbo.TB_Events.EV_PostAs,
> dbo.TB_Events.EV_StartDate, dbo.TB_Events.EV_EndDate,
> dbo.TB_Events.EV_Status,
> dbo.TB_Events.EV_StatusDate,
> dbo.TB_Events.EV_ProspectDate, dbo.TB_Events.EV_TentativeDate,
> dbo.TB_Events.EV_DefiniteDate,
> dbo.TB_Events.EV_HistoricDate,
> dbo.TB_Events.EV_CancelDate, dbo.TB_Events.EV_ProposalCreateDate,
> dbo.TB_Events.EV_ContractCreateDate,
> dbo.TB_Events.EV_CutoffDate,
> dbo.TB_Events.EV_EventSummary, dbo.TB_Events.EV_BookingSource,
> dbo.TB_Events.EV_EventProfile,
> dbo.TB_Events.EV_EventFrequency,
> dbo.TB_Events.EV_BookingLead, dbo.TB_Events.EV_ReservationMethod,
> dbo.TB_Events.EV_BookingCode,
> dbo.TB_Events.EV_SpecialRequests,
> dbo.TB_Events.EF_MarketSegment
> FROM dbo.TB_Events INNER JOIN
> dbo.TB_Accounts ON dbo.TB_Events.EV_AccountId =
> dbo.TB_Accounts.MA_AccountId'
>
|||the solution there is very intresting!
i have looked for something like that in the past and didnt find 1.
thnaks
Peleg
"Kalen Delaney" wrote:

> Hi RJ
> There are special issues with getting output values back through
> sp_executesql. This KB article explains how to use output parameters with a
> stored procedure. I haven't tried using it with functions, but since you
> just want a value returned, you could turn your function into a procedure
> and have the return value be an output parameter.
> "How to specify output parameters when you use the sp_executesql stored
> procedure in SQL Server"
> http://support.microsoft.com/kb/262499/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "RJ" <RJ@.discussions.microsoft.com> wrote in message
> news:0A7BAFB6-5E44-4ED0-96E6-892701C44CBD@.microsoft.com...
>
>
|||Hi Kalen,
Thank You for your response. I took the rest of the day off yesterday but
now I am back at it!
I am looking into what you suggested however I still don't understand why
the dynamic sql did not either a) execute the inline function call or b)
return an error. Although I have worked in depth with Oracle, I am a
relative rookie to SQL Server. So whatever info you can pass on is
appreciated.
Thank again,
RJ
"Kalen Delaney" wrote:

> Hi RJ
> There are special issues with getting output values back through
> sp_executesql. This KB article explains how to use output parameters with a
> stored procedure. I haven't tried using it with functions, but since you
> just want a value returned, you could turn your function into a procedure
> and have the return value be an output parameter.
> "How to specify output parameters when you use the sp_executesql stored
> procedure in SQL Server"
> http://support.microsoft.com/kb/262499/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "RJ" <RJ@.discussions.microsoft.com> wrote in message
> news:0A7BAFB6-5E44-4ED0-96E6-892701C44CBD@.microsoft.com...
>
>
|||RJ
I misunderstood the original question. I thought the entire SQL string was a
function call, and now I see that there are calls embedded in your long
example. How do you know the inline function was not executed? Are all the
other columns being returned? Although your code is extremely difficult to
read, I see 3 places where a function is called. Are all three of those
values just 'missing' from the output? In the future, please be explicit
about exactly what is happening, and try to simplify your problem as much as
possible. We obviously cannot run your code to do any testing as we don't
have the tables.
You could set up a trace which will show you if the function is being
called.
I would suggest you try a much simpler example for verification. Perhaps
just select the function and one other column from the table.
Also, in the future, please always state what version and service pack you
are using.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"RJ" <RJ@.discussions.microsoft.com> wrote in message
news:46C212E3-283E-4FC1-9F4A-0BA0D42A58CB@.microsoft.com...[vbcol=seagreen]
> Hi Kalen,
> Thank You for your response. I took the rest of the day off yesterday but
> now I am back at it!
> I am looking into what you suggested however I still don't understand why
> the dynamic sql did not either a) execute the inline function call or b)
> return an error. Although I have worked in depth with Oracle, I am a
> relative rookie to SQL Server. So whatever info you can pass on is
> appreciated.
> Thank again,
> RJ
> "Kalen Delaney" wrote:
|||You're welcome!
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"pelegk1" <pelegk1@.discussions.microsoft.com> wrote in message
news:6CA0479F-3249-4A2D-9D24-7AE6ACB9AD78@.microsoft.com...[vbcol=seagreen]
> the solution there is very intresting!
> i have looked for something like that in the past and didnt find 1.
> thnaks
> Peleg
>
> "Kalen Delaney" wrote:
|||I have a complex code-gen proc that creates dynamci SQL and then executes it
with sp_ExecuteSQL that includes a table-valued multi-line UDF and I've
never had any trouble with the UDF returning data. btw, I recently converted
an in-line UDF with parameters to a multi-line UDF and it runs much faster.
-Paul
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23c4dd8IUIHA.3916@.TK2MSFTNGP02.phx.gbl...
> RJ
> I misunderstood the original question. I thought the entire SQL string was
> a function call, and now I see that there are calls embedded in your long
> example. How do you know the inline function was not executed? Are all
> the other columns being returned? Although your code is extremely
> difficult to read, I see 3 places where a function is called. Are all
> three of those values just 'missing' from the output? In the future,
> please be explicit about exactly what is happening, and try to simplify
> your problem as much as possible. We obviously cannot run your code to do
> any testing as we don't have the tables.
> You could set up a trace which will show you if the function is being
> called.
> I would suggest you try a much simpler example for verification. Perhaps
> just select the function and one other column from the table.
> Also, in the future, please always state what version and service pack you
> are using.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "RJ" <RJ@.discussions.microsoft.com> wrote in message
> news:46C212E3-283E-4FC1-9F4A-0BA0D42A58CB@.microsoft.com...
>
|||This apparently is a scalar UDF so it's neither inline nor multiline. We're
still waiting to hear back from the OP exactly what the results are.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Paul Nielsen (SQL)" <pauln@.sqlserverbible.com> wrote in message
news:8A7ED610-875B-4192-985D-8727DA2CDBD4@.microsoft.com...
>I have a complex code-gen proc that creates dynamci SQL and then executes
>it with sp_ExecuteSQL that includes a table-valued multi-line UDF and I've
>never had any trouble with the UDF returning data. btw, I recently
>converted an in-line UDF with parameters to a multi-line UDF and it runs
>much faster.
> -Paul
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23c4dd8IUIHA.3916@.TK2MSFTNGP02.phx.gbl...
>
|||Thank you all for your help. I am not sure what exactly I did, as I tried a
lot of things, but it now works just fine.
The problem when I had it was that it was returning all the columns
including the ones from the called function however the column values from
the called function were all null. The function was written to return some
value (not null) even if no valid contact was found. Like I said earlier,
when I ran the script in Analyzer the function columns came back with the
correct values.
Perhaps the issue was in my app that was calling the sp. Anyway, it all
works as expected as there is no problem calling a UDF from dynamic sql.
Thanks again,
RJ
"Kalen Delaney" wrote:

> This apparently is a scalar UDF so it's neither inline nor multiline. We're
> still waiting to hear back from the OP exactly what the results are.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Paul Nielsen (SQL)" <pauln@.sqlserverbible.com> wrote in message
> news:8A7ED610-875B-4192-985D-8727DA2CDBD4@.microsoft.com...
>
>
|||And yes you were correct that it is a scalar UDF. Like I said I am still
learning SQL Server terminology.
"Kalen Delaney" wrote:

> This apparently is a scalar UDF so it's neither inline nor multiline. We're
> still waiting to hear back from the OP exactly what the results are.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Paul Nielsen (SQL)" <pauln@.sqlserverbible.com> wrote in message
> news:8A7ED610-875B-4192-985D-8727DA2CDBD4@.microsoft.com...
>
>