Dynamic SQL Command: builds

Showing posts with label builds. Show all posts

Wednesday, March 7, 2012

Dynamic where clause with if..else or case

Hello all...

I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select

i.e

if...@.where = @.where + ' llll '

select @.statement = @.statement + @.where

exec(@.statement)

I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.

Is it possible to use either statement inside a where clause?

i.e

where if a = 1 then d=e

else contains(.....)

thanks

No. You cannot use control of flow statements in the WHERE clause. CASE is an expression so you can use it in the WHERE clause. Since CONTAINS is a predicate in itself, you need to do something like:

case ... when .. then (select 1 where contains()) else ... end = 1

But it is not clear why you need to use dynamic SQL or build WHERE clause like this. Using dynamic SQL can cause performance problems and has lot of security implications. So you need to be aware of the risks while using it. CONTAINS predicate in SQL Server 2000 (from SP3 I think) and SQL Server 2005 can take a variable for the search expression so you probably don't dynamic SQL for this. If you have different checks to perform then you may actually be better off using multiple IF statements that call specific SPs with SELECT statements. This will provide the best performance advantage.

|||

Thanks. I need the dynamic where based on search criteria because it was decided to make one SP compared to about 50 or more because there are possible combinations of the criteria...

The purpose of the contains is to search a text field for a value that is close to what what asked for. This is one of our slowest SPs so I thought I would give it a shot to make it quicker but I guess I will have to just be happy with what it is.

Thanks for the help.

|||

Check out the article at the link below for various techniques on how to do dynamic searches using SPs.

http://www.sommarskog.se/dyn-search.html

Wednesday, February 15, 2012

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(dbo.TB_Events.EV_AltContactId,1) as AltContact,
dbo.TB_Events.EV_OnSiteContactId,
dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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(dbo.TB_Events.EV_AltContactId,1)
> as AltContact,
> dbo.TB_Events.EV_OnSiteContactId,
> dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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...
> >
> >
> > 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(dbo.TB_Events.EV_AltContactId,1)
> > as AltContact,
> > dbo.TB_Events.EV_OnSiteContactId,
> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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 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...
> >
> >
> > 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(dbo.TB_Events.EV_AltContactId,1)
> > as AltContact,
> > dbo.TB_Events.EV_OnSiteContactId,
> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
> >
>
>|||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...
> 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...
>> >
>> >
>> > 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(dbo.TB_Events.EV_AltContactId,1)
>> > as AltContact,
>> > dbo.TB_Events.EV_OnSiteContactId,
>> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
>> >
>>|||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...
> 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...
>> >
>> >
>> > 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(dbo.TB_Events.EV_AltContactId,1)
>> > as AltContact,
>> > dbo.TB_Events.EV_OnSiteContactId,
>> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
>> >
>>|||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...
>> 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...
>> >
>> >
>> > 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(dbo.TB_Events.EV_AltContactId,1)
>> > as AltContact,
>> > dbo.TB_Events.EV_OnSiteContactId,
>> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
>> >
>>
>|||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...
>> 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...
>> 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...
>> >
>> >
>> > 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(dbo.TB_Events.EV_AltContactId,1)
>> > as AltContact,
>> > dbo.TB_Events.EV_OnSiteContactId,
>> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
>> >
>>
>>
>|||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...
> >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...
> >> 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...
> >> >
> >> >
> >> > 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(dbo.TB_Events.EV_AltContactId,1)
> >> > as AltContact,
> >> > dbo.TB_Events.EV_OnSiteContactId,
> >> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
> >> >
> >>
> >>
> >>
> >>
> >>
> >
>
>|||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...
> >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...
> >> 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...
> >> >
> >> >
> >> > 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(dbo.TB_Events.EV_AltContactId,1)
> >> > as AltContact,
> >> > dbo.TB_Events.EV_OnSiteContactId,
> >> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
> >> >
> >>
> >>
> >>
> >>
> >>
> >
>
>|||Thanks for letting us know, and I'm glad it's working now!
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"RJ" <RJ@.discussions.microsoft.com> wrote in message
news:6E30FD55-ADE2-48D3-A616-E1ACF6B00628@.microsoft.com...
> 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...
>> >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...
>> >> 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...
>> >> >
>> >> >
>> >> > 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(dbo.TB_Events.EV_AltContactId,1)
>> >> > as AltContact,
>> >> > dbo.TB_Events.EV_OnSiteContactId,
>> >> > dbo.fn_ContactName(dbo.TB_Events.EV_OnSiteContactId,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'
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>>

Wednesday, March 7, 2012

Dynamic where clause with if..else or case

Wednesday, February 15, 2012

Dynamic SQL calling a function

Dynamic SQL calling a function

Dynamic SQL Command

Blog Archive

About Me