Dynamic SQL Command: function

Showing posts with label function. Show all posts

Monday, March 19, 2012

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

Dynamically passing a Parameter Name to Custom Code

Hello,

I'm trying to create a custom code function for Reporting Services. I would like to have it user-friendly and give the user the ability to pass a report parameter name into the function (so it can be a generic function that can be used for many reports).

Is there a way to do this so inside the code I can have access to other properties of the object?

I envision something like:

Function Blah(ParameterName as String) as String

Dim MaxNum as Integer
Dim ParamValue as String

MaxNum = Reports.Parameters!ParameterName.Count - 1
ParamValue = Reports.Parameters!ParameterName.Value

etc...

Is this possible? I can't figure out how to do this. Using dynamic SQL you can do this very easily by concatenating string values together and then executing the string. Is there something similar to this in VB?

Help! Thanks

Below is an example for a custom code function that you can call in a textbox e.g. as

=Code.ShowParametersValues(Parameters!Country)

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

-- Robert

Dynamically Modify the Datepart Argument of DATEADD function in T-SQL

Is it possible to dynamically modify the Datepart argument of the DATEADD function in T-SQL 2005.

I am trying to do something like this but it's not happy with parameter 1:

DECLARE @.pp NCHAR(3)
SET @.pp = 'day'
SELECT dateadd(@.pp,3,'2007-07-14 12:00:00')

Any Ideas will be much appreciated.

Quote:

Originally Posted by kevinSQL

Is it possible to dynamically modify the Datepart argument of the DATEADD function in T-SQL 2005.

I am trying to do something like this but it's not happy with parameter 1:

DECLARE @.pp NCHAR(3)
SET @.pp = 'day'
SELECT dateadd(@.pp,3,'2007-07-14 12:00:00')

Any Ideas will be much appreciated.

put the entire select command to a variable

Friday, March 9, 2012

Dynamically create a database field

Hi,

Does any one know how to create a database field name dynamically or is there a function in crystal similar to the "eval" function in javascript.

I am tyring to loop thru 10 fields ... key0 thru key9 and I want to dynamically create them

Local StringVar str := "";

Local NumberVar i;
For i := 0 To 9 Do
(

str := str + totext({tablename.key[i]})
);
str

Eventually I want to get the value for str.

thanksI think you are trying to concatenate the string values
If so, there is no other way other than specifing the field names|||Yes, that is what I am trying to do... thanks anyway

Wednesday, March 7, 2012

Dynamicallly Importing csv file into MS SQL

I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need

The user needs a function to import Contact data from a txt/csv file into the Contact Table

Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields

Then i need to use those parameters and run some sort of import routine putting the data into the specific table.

The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above

How do I do this using MS SQL??

I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Is this the correct command to use??

Do you know any websites that can point me in the right direction??

ThanksIt's syntax error.
Use () instead of {} as below:

BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)|||if you still hit error, try to see if you have grant the right permission in Windows for your csv files.

Sunday, February 19, 2012

Dynamic SQL within a function

Hello,
I need to create a view on a database based on tables from other databases.
I'm creating a function to lookup the data in the other dbs and use the
function on the view. This is the function.
CREATE FUNCTION CTI_InventoryView ()
RETURNS @.CTI_Inventory TABLE
(INTERID VARCHAR(5), CMPNYNAM VARCHAR(65),
ITEMNMBR VARCHAR(31), ITEMDESC VARCHAR(101),
LOCNCODE VARCHAR(11), RCRDTYPE SMALLINT,
PRIMVNDR VARCHAR(15), LSORDQTY NUMERIC(19,5),
LRCPTQTY NUMERIC(19,5), LSTORDDT DATETIME,
LSORDVND VARCHAR(15), LSRCPTDT DATETIME,
QTYRQSTN NUMERIC(19,5), QTYONORD NUMERIC(19,5),
QTYBKORD NUMERIC(19,5), QTY_Drop_Shipped NUMERIC(19,5),
QTYINUSE NUMERIC(19,5), QTYINSVC NUMERIC(19,5),
QTYRTRND NUMERIC(19,5), QTYDMGED NUMERIC(19,5),
QTYONHND NUMERIC(19,5), ATYALLOC NUMERIC(19,5),
QTYAVAIL NUMERIC(19,5), QTYCOMTD NUMERIC(19,5),
QTYSOLD NUMERIC(19,5))
AS
BEGIN
DECLARE
@.INTERID VARCHAR(5),
@.COMPANYNAME VARCHAR(65),
@.SQL NVARCHAR(1000)
DECLARE CTI_Companies CURSOR FOR
SELECT INTERID, CMPNYNAM
FROM DYNAMICS..SY01500
OPEN CTI_Companies
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'USE ' + @.INTERID + 'SELECT ' + ''''+ @.InterID + '''' + ', '
+ '''' + @.CompanyName + ''''
SELECT @.SQL = @.SQL + 'IV00102.ITEMNMBR, ITEMDESC, IV00102.LOCNCODE,
RCRDTYPE, PRIMVNDR, '
SELECT @.SQL = @.SQL + 'LSORDQTY, LRCPTQTY, LSTORDDT, LSORDVND, LSRCPTDT,
QTYRQSTN, QTYONORD, QTYBKORD, '
SELECT @.SQL = @.SQL + 'QTY_Drop_Shipped, QTYINUSE, QTYINSVC, QTYRTRND,
QTYDMGED, QTYONHND, ATYALLOC, '
SELECT @.SQL = @.SQL + 'QTYONHND - ATYALLOC, QTYCOMTD, QTYSOLD '
SELECT @.SQL = @.SQL + 'FROM IV00102 JOIN IV00101 ON IV00102.ITEMNMBR =
IV00101.ITEMNMBR '
INSERT INTO @.CTI_Inventory
EXECUTE sp_executesql @.SQL
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
END
CLOSE CTI_Companies
DEALLOCATE CTI_Companies
RETURN
END
GO
I found that I cannot call the sp_executesql stored procedure inside the
function. I receive this error
Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
within a function.
I'm running out of ideas to do this, any ideas how to do this?,
Thanks,
Sandra.Sandra Parra (sparra@.citrinetech.com) writes:
> I need to create a view on a database based on tables from other
> databases.
> I'm creating a function to lookup the data in the other dbs and use the
> function on the view. This is the function.
>...
> I found that I cannot call the sp_executesql stored procedure inside the
> function. I receive this error
> Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
> Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
> within a function.
> I'm running out of ideas to do this, any ideas how to do this?,
Why are there so many databases, and why do need a view over all them?
I ask this question to possibly be able to give a better response.
From what I see here, I can think of two ways:
1) The number of databases is so dynamic, that you need a iterate over
all databases for every query. In such case, write a stored procedure
and get data into a temp table.
2) The number of databases is static enough, so you can set up a
job that defines a view every night, so that run can run queries
on this view during the day.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, February 17, 2012

Dynamic SQL into a cursor

basically im creating a user defined function that i want to take the parameters that are passed to it to create the sql for a cursor to loop through. As far as i can tell a cursor cannot take dynamic sql, anyone got any bright ideas or an easy way round this?

My code so far is as follows (the variable @.sql is the sql statement i want passed in to the cursor):

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

set @.sql = 'select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

declare archivecur cursor
for
@.sql

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
right i thought id found the answer to append the rows from the dynamic sql into a table variable and loop through that but unfortunately im now getting must declare the variable @.curtable

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

declare @.curtable table (Field1 varchar(50), modifdate varchar(8))

set @.sql = 'insert into @.curtable (field1, modifdate) convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + convert(varchar(20),@.inprefno) + ' order by modif_dttm'

execute(@.sql)

declare archivecur cursor
for
select field1, modifdate
from @.curtable
order by modifdate

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
|||

Lamffy,

In order to execute dynamic sql, you need EXEC(...) or sp_executesql and no one of them could be used inside a user defined function.

To play with a cursor and dynamic sql , you can define and open the cursor inside the dynamic sql and return it in an output variable.

Example:

Code Snippet

use northwind

declare @.sql nvarchar(4000)

declare @.customerid nchar(5)

declare @.c cursor

declare @.orderid int

set @.sql = N'

set @.c = cursor local fast_forward

for

select orderid

from dbo.orders

where customerid = @.customerid;

open @.c'

exec sp_executesql @.sql, N'@.customerid nchar(5), @.c cursor output', 'ALFKI', @.c output

if cursor_status('variable', '@.c') = 1

begin

while 1 = 1

begin

fetch next from @.c into @.orderid

if @.@.error != 0 or @.@.fetch_status != 0 break

print @.orderid

end

close @.c

deallocate @.c

end

what are you trying to accomplish, creating a user defined function that involves dynamic sql and cursors?

AMB

|||

Try

set @.sql = 'declare archivecur cursor for
select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

exec (@.sql)

open archivecur

|||

Maybe it's me, but this seems like extreme overkill.

What function or looping is required to carry out this task?

I'm thinking a SELECT CASE would accomplish what you are looking for.

Adamus

|||Cheers Mark that worked. You're a lifesaver!! :-)
|||I agree Adamus it does look a bit like overkill but when you see the state of NHS data and the data warehouses it's all stored in you'd understand how much of a nightmare doing anything is!

Wednesday, February 15, 2012

Dynamic SQL in UDF

Does anyone know how to execute dynamic SQL in a user defined function? Here's a quick example of what I would like to do. I get an error that only functions and extended stored procedures may be called in a function. Is there any other way to execute dynamic SQL in a UDF?

CREATE function dbo.test(@.table char(40), @.value char(40))
RETURNS int
AS
BEGIN
DECLARE @.return char(3)
DECLARE @.sqlstring nvarchar(500)

SET @.sqlstring = 'Select count(*) From @.table Where id = @.value'

Execute sp_executesql @.sqlstring

RETURN(@.return)
ENDHow would you use this function?

SELECT dbo.Test('a','b')

?

Why not just do

EXEC @.rc = Test 'a','b'|||I'm not sure I follow your reply. I want to call the UDF from a table constraint.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.

UDF's do not support dynamic sql. You would have to go with a stored procedure in order to gain that flexibility. I think they have that somewhere on msdn too..|||I can't find the msdn outline... here's a limitation rundown from informit (http://www.informit.com/isapi/product_id~{0D83BA18-CDB2-4D74-9C2A-AA44581B27B9}/element_id~{43287535-9508-43D6-BB36-4852A7A9F91B}/st~{340C91CD-6221-4982-8F32-4A0A9A8CF080}/session_id~{65960459-451E-4EF6-9D3B-7E7CF0E4CB0B}/content/articlex.asp) that I found to be pretty comprehensive.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.

A CONSTRAINT? What would that do?

what are you trying to restrict?|||I'm trying to restrict date ranges from overlapping in several tables. I was hoping to use dynamic sql in a udf so that the udf can be reused by multiple tables.

Here's an example: A salesman can be licensed in a particular state to sell widgets. His licensed can be terminated and re-instated in a state. However, he can't hold two licenses in the same state at the same time. Our system must track each instance of a license the salesman has had in every state. Our developers didn't think validating overlapping start and end dates was important so no validation exists in the java code. Hence we have bad data with overlapping date ranges for a salesman in a state.
**Table Structure (not syntactically correct):
id int(pk identity)
salesman_id int
state_id int
start_date datetime
end_date datetime

I would like the table constraint to call the udf which would return if there were any date ranges overlapping the inserted or updated data. If so prevent the insert or update. Oh yeah, this would prevent me from having to code a trigger on each table this logic will be used.

Hope this helps!|||Why not just create a unique index on saleman id, State?

use an update trigger to move to current row to a history table where it's not unique?

The create a view if the need to see all of the data.

If someone tries to add another salesman that's already in the same state, they'll get an exception...

What they probably should be doing is an update not an insert anyway

MOO|||Yeah, that would work but I would have to do that for every table where this occurs. I was looking for an 'easier' solution that may end up being just as complex. I would prefer not to have to maintain seperate history tables nor triggers on each table but thanks for the ideas.

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

Monday, March 19, 2012

Dynamically selecting a row from a table

Dynamically passing a Parameter Name to Custom Code

Dynamically Modify the Datepart Argument of DATEADD function in T-SQL

Friday, March 9, 2012

Dynamically create a database field

Wednesday, March 7, 2012

Dynamicallly Importing csv file into MS SQL

Sunday, February 19, 2012

Dynamic SQL within a function

Friday, February 17, 2012

Dynamic SQL into a cursor

Wednesday, February 15, 2012

Dynamic SQL in UDF

Dynamic SQL calling a function

Dynamic SQL calling a function

Dynamic SQL Command

Blog Archive

About Me