Easier way of building pivot tables in MS SQL Server

Dear All

I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.

Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?

I would be very grateful for any help with this.

wujtehacjuszWhat version of SQL Server?

SQL Server 2005 has the PIVOT command.

On Jul 10, 5:19 am, wujtehacjusz <wujtehacj...@.gmail.comwrote:

Quote:

Originally Posted by

Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz

|||For SQL 2000, check out http://www.rac4sql.net/
--
Hope this helps.

Dan Guzman
SQL Server MVP

"wujtehacjusz" <wujtehacjusz@.gmail.comwrote in message
news:1184059198.208520.109590@.q75g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
>

each time I build program, data is lost

I am using visual basic 2008

I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,

when I close the program and build again, the data previosly updated are deleted, and I get empty database? why is that. do i need to set some copy to.........properties. i have used copy if new.

I want to add something to it, that when I manually entered data, they are not gone. But when I entered from form, next time the data lost.|||

See this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2115234&SiteID=1

Dynamicly build report against Reporting Services

Hello Every one,
I would like to know if it's possible to dynamicly build report againt
RS ?
Thanks alots
Best Regards and seasons greetings!
Patrice Lamarche
Groupe Conseil OmnitechPossible but not that easy. Here is what you have to do today. First, you
need to learn the rdl specification (you can find that on MS site) which is
XML and create the report yourself. Then you have to deploy it using web
services. Since it is a server based solution you have to deploy it with a
unique name so that only the user you want to use it uses it. So, two things
skills you have to have for this: web services and creating the rdl on the
fly. With Yukon there will be both a winform and webform control that will
work with the server if it is there but does not require it. In theory (I
have not use it since it is not even out in beta yet) you should be able to
give it the rdl. So with Yukon you at least do not have the hoops to jump
through on deploying the report you generated.
What are you wanting to do that requires dynamically building the report. If
it is data you have the option of filtering the data based on the user.
Also, there are many many places that expressions can be used in the report
that allow a degree of dynamic changes to the report.
My advice is only go this route if you have to.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Patrice Lamarche" <Patrice_lamarche@.Removethis.gco.ca> wrote in message
news:unkvErc6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hello Every one,
> I would like to know if it's possible to dynamicly build report againt RS
> ?
> Thanks alots
> Best Regards and seasons greetings!
> Patrice Lamarche
> Groupe Conseil Omnitech|||In addition to what Bruce said, this is how I addressed a similiar problem
in the past. The customer wanted to have a report with sections which are
shown on demand, that is the user could select which sections she wants to
see. To minimize development effort, I decideded to have a report template
which has all the sections. Then, it was just a matter of loading the report
RDL in XML DOM and removing the section elements you don't need. I addition,
I had to take care of re-positioning (moving up) the remaining sections to
fill in the emtpy space left by the deleted sections.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O9bMOLd6EHA.2552@.TK2MSFTNGP09.phx.gbl...
> Possible but not that easy. Here is what you have to do today. First, you
> need to learn the rdl specification (you can find that on MS site) which
> is XML and create the report yourself. Then you have to deploy it using
> web services. Since it is a server based solution you have to deploy it
> with a unique name so that only the user you want to use it uses it. So,
> two things skills you have to have for this: web services and creating the
> rdl on the fly. With Yukon there will be both a winform and webform
> control that will work with the server if it is there but does not require
> it. In theory (I have not use it since it is not even out in beta yet) you
> should be able to give it the rdl. So with Yukon you at least do not have
> the hoops to jump through on deploying the report you generated.
> What are you wanting to do that requires dynamically building the report.
> If it is data you have the option of filtering the data based on the user.
> Also, there are many many places that expressions can be used in the
> report that allow a degree of dynamic changes to the report.
> My advice is only go this route if you have to.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Patrice Lamarche" <Patrice_lamarche@.Removethis.gco.ca> wrote in message
> news:unkvErc6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Hello Every one,
>> I would like to know if it's possible to dynamicly build report againt RS
>> ?
>> Thanks alots
>> Best Regards and seasons greetings!
>> Patrice Lamarche
>> Groupe Conseil Omnitech
>|||You can generate the RDL/XML on the fly using the RDL reader/writer
http://www.rdlcomponents.com
Jerry
"Teo Lachev [MVP]" wrote:
> In addition to what Bruce said, this is how I addressed a similiar problem
> in the past. The customer wanted to have a report with sections which are
> shown on demand, that is the user could select which sections she wants to
> see. To minimize development effort, I decideded to have a report template
> which has all the sections. Then, it was just a matter of loading the report
> RDL in XML DOM and removing the section elements you don't need. I addition,
> I had to take care of re-positioning (moving up) the remaining sections to
> fill in the emtpy space left by the deleted sections.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O9bMOLd6EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > Possible but not that easy. Here is what you have to do today. First, you
> > need to learn the rdl specification (you can find that on MS site) which
> > is XML and create the report yourself. Then you have to deploy it using
> > web services. Since it is a server based solution you have to deploy it
> > with a unique name so that only the user you want to use it uses it. So,
> > two things skills you have to have for this: web services and creating the
> > rdl on the fly. With Yukon there will be both a winform and webform
> > control that will work with the server if it is there but does not require
> > it. In theory (I have not use it since it is not even out in beta yet) you
> > should be able to give it the rdl. So with Yukon you at least do not have
> > the hoops to jump through on deploying the report you generated.
> >
> > What are you wanting to do that requires dynamically building the report.
> > If it is data you have the option of filtering the data based on the user.
> > Also, there are many many places that expressions can be used in the
> > report that allow a degree of dynamic changes to the report.
> >
> > My advice is only go this route if you have to.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Patrice Lamarche" <Patrice_lamarche@.Removethis.gco.ca> wrote in message
> > news:unkvErc6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >> Hello Every one,
> >>
> >> I would like to know if it's possible to dynamicly build report againt RS
> >> ?
> >>
> >> Thanks alots
> >>
> >> Best Regards and seasons greetings!
> >>
> >> Patrice Lamarche
> >> Groupe Conseil Omnitech
> >
> >
>
>

Dynamically name folders

Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.

I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.

Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.

Thanks for the help,

Chris

Use a foreach loop.

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||

Thanks!!!

That was exactly what I needed and worked like a charm.

Chris

dynamically created dataset

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

Hi,

Starting with SQL Server 2005, Microsoft has added rdlc reports. These reports can be embedded within a C# application by using the reportviewer.

Here is an example: http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx.

References: http://msdn2.microsoft.com/en-us/library/ms251671(VS.80).aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I've messed around with the rdlc reports w/ the reportviewer but i don't see how that would help me.

the report viewer lets you swap which report is shown in the viewer, but i want to swap the dataset that the report gets its data from.|||What i ended up doing is making a stored procedure that accepted a 'where clause' parameter that was built in the application. i then use the SP as the data source for the report.

IF @.WhereClause + '' <> '' SET @.statement = @.statement + ' AND ' + @.WhereClause|||Several in fact as explained in this article.

dynamically created dataset

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

Hi,

Starting with SQL Server 2005, Microsoft has added rdlc reports. These reports can be embedded within a C# application by using the reportviewer.

Here is an example: http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx.

References: http://msdn2.microsoft.com/en-us/library/ms251671(VS.80).aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I've messed around with the rdlc reports w/ the reportviewer but i don't see how that would help me.

the report viewer lets you swap which report is shown in the viewer, but i want to swap the dataset that the report gets its data from.|||What i ended up doing is making a stored procedure that accepted a 'where clause' parameter that was built in the application. i then use the SP as the data source for the report.

IF @.WhereClause + '' <> '' SET @.statement = @.statement + ' AND ' + @.WhereClause|||Several in fact as explained in this article.

Dynamic Where clause

Hello everyone,

I want to build a dynamic where clause which makes :

WHERE column1 = (@.parameter1 if @.parameter1 is not null) / (anything if @.parameter1 is null)

Basically I do not know how to set column1 = ANYTHING

Best regards and thanks.

Maybe something like

Code Snippet

Where @.parmameter1 is null

or @.parameter1 is not null and column1 = @.parameter1

Another alternative would be to use IF / ELSE around two distinct select statements rather than this particular WHERE clause. I suspect that this where syntax (and also the COALESCE syntax) will precipitate a SCAN instead of a seek. If your table is very small this won't matter.

You might be able to avoid the SCAN when you pass the parameter by using the IF / ELSE syntax PROVIDED that you have an index on column1.

|||

You can also use the coalesce function to determine which value to use. Here is an example using sys.databases

Code Snippet

DECLARE @.param1 NVARCHAR(20)

SET @.param1 = 'master'

-- SET @.param1 = NULL

SELECT * FROM sys.databases

WHERE name = COALESCE(@.param1, name)

Try it with both parameter settings. One will return just the row for master, the other will return all rows.

|||@. Kent Waldrop Thank you very much for this trick.. It seems simple but does a lot !

Dynamic Where clause

I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
Eep.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html|||You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
store the exec string in a variable and then execute it:
DECLARE @.str varchar (8000)
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
EXEC (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END|||Tom,
I get this error message'?
Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
Invalid operator for data type. Operator equals boolean AND, type equals
varchar.
Joshua
"Tom Moreau" wrote:

> You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
> store the exec string in a variable and then execute it:
> DECLARE @.str varchar (8000)
> set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode'
> & @.WhereOrderByClause
> EXEC (@.str)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC
'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>
>|||Try something like this in case because the first one may not exist
set @.WhereOrderByClause = ' WHERE 1=1'
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
+ @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'|||I've already fixed the other part... this is what i'm trying:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
Declare @.str varchar (8000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + 'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
-- Insert statements for procedure here
EXEC (@.str)
END
"JeffB" wrote:

> Try something like this in case because the first one may not exist
> set @.WhereOrderByClause = ' WHERE 1=1'
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
> + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
>|||& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||What if @.ProgName is NULL and @.ProgNo is 3? Then the sql generated
will be 'WHERE AND p.ProgNo IN (3)' which won't work. The initial
setting should be 'WHERE 1 = 1
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause +
'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND
p.ProgNo IN (' +
@.ProgNo + ')'|||Doh! I do that a lot - jumping back and forth between VB and T-SQL. :-S
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eReETs6RGHA.1948@.TK2MSFTNGP09.phx.gbl...
& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||Use = instead of in, unless you are actually dealing with a list of values
contained within a string. It works either way, but is easier to understand
with the =.
When concatenating the string together, you need to place quotes around your
values, or pass them explicitly as parameters with sp_executesql. Two
single quotes are used to represent single quotes within quotes.
i.e.
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = ''' + @.ProgName + ''''
the resulting strign is
WHERE p.ProgName = 'Test'
or, pass the parameters to the sp_executesql:
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = @.ProgName'
set @.SelectString = 'Select * from SomeTable ' + @.WhereOrderByClause
SET @.ParmDefinition = '@.ProgName varchar(300)'
/* Execute the string with the first parameter value. */
EXECUTE sp_executesql @.SelectString, @.ParmDefinition, @.ProgName =
@.ProgName
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn
ASC'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>

Dynamic T-SQL steatment

How can i build a T-SQL statement dynamically?

Vivek S

Do you mean within SSIS? You should use an expression: http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=

If you mean to be used in (e.g.) a sproc then you're on the wrong forum. Try the T-SQL forum.

-Jamie

|||i have declared a variable of type string and initialised it with a T-SQL statement. The sql statement size is around 5000 characters. To have some run time modifications, i have used a script task and initialised the SQL to the variable so that it can take the changes at run time. i have used a DataflowTask within which accessing the variable for the SQL. But when i execute the ssis pkg it throws an error at the dft validation level as VS_ISBroken.|||

What type of component is using the SQL statement?

What component is failing?

What is the error?

Please supply more pertinent information otherwise its a bit dificult to be of assistance.

As an aside, you really should consider using an expression rather than a script task to dynamically build your SQL statement.

-Jamie

Dynamic SQL statements over 4000 chars

Hi, All,
I am using SQL Server 2000.
I want to dynamically build a SQL statement for pivot query, which come out
more than the character limits for variable and sp_executesql..
What I need is: a query to return all security_level for all User_Id for all
screen_id. We have more than 280 user accounts in AcctUsers table. The
Output format need to be:
Screen User1 User2 User3 .....
Window1 0 1 2
Window2 3 2 0
I used cursor to get user_ids from AcctUsers table, then dynamically build
the SQL statment based on the User_ID. It works fine for up to about 40
user_ids. But it out off the chararter limit for variable nverachar and
sp_executesql. The way that I choose dynamical SQL because the business user
may add new user_id to AcctUsers table and then run a report, which will
call this query.
Can anyone help me?
Thanks.
Perayu
Here are the DDL:
CREATE TABLE [dbo].[AcctUsers] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Security] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[screen_id] [smallint] NOT NULL ,
[security_level] [smallint] NOT NULL
) ON [PRIMARY]
GOHi!
Do please check this: http://www.sommarskog.se/dynamic_sql.html#use-which.
I would suggest you to read the complete article.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi, All,
> I am using SQL Server 2000.
> I want to dynamically build a SQL statement for pivot query, which come
> out more than the character limits for variable and sp_executesql..
> What I need is: a query to return all security_level for all User_Id for
> all screen_id. We have more than 280 user accounts in AcctUsers table. The
> Output format need to be:
> Screen User1 User2 User3 .....
> Window1 0 1 2
> Window2 3 2 0
> I used cursor to get user_ids from AcctUsers table, then dynamically build
> the SQL statment based on the User_ID. It works fine for up to about 40
> user_ids. But it out off the chararter limit for variable nverachar and
> sp_executesql. The way that I choose dynamical SQL because the business
> user may add new user_id to AcctUsers table and then run a report, which
> will call this query.
> Can anyone help me?
> Thanks.
> Perayu
> Here are the DDL:
> CREATE TABLE [dbo].[AcctUsers] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Security] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [screen_id] [smallint] NOT NULL ,
> [security_level] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>|||Great article!
Here is the trick that I will try, nesting EXEC:
DECLARE @.sql1 nvarchar(4000),
@.sql2 nvarchar(4000),
@.state char(2)
SELECT @.state = 'CA'
SELECT @.sql1 = N'SELECT COUNT(*)'
SELECT @.sql2 = N'FROM authors WHERE state = @.state'
EXEC('EXEC sp_executesql N''' + @.sql1 + @.sql2 + ''',
N''@.state char(2)'',
@.state = ''' + @.state + '''')
Thanks for your help!
Perayu
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:Oqh49AUPGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi!
> Do please check this: http://www.sommarskog.se/dynamic_sql.html#use-which.
> I would suggest you to read the complete article.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of
> the sender.
> This message does not imply endorsement from Solid Quality Learning, and
> it does not represent the point of view of Solid Quality Learning or any
> other person, company or institution mentioned in this message
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||If unicode is not a requirement, you can store 8000 chars in varchar. The
unicode nvarchar will only store 4000.
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eL2e9rTPGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi, All,
> I am using SQL Server 2000.
> I want to dynamically build a SQL statement for pivot query, which come
> out more than the character limits for variable and sp_executesql..
> What I need is: a query to return all security_level for all User_Id for
> all screen_id. We have more than 280 user accounts in AcctUsers table. The
> Output format need to be:
> Screen User1 User2 User3 .....
> Window1 0 1 2
> Window2 3 2 0
> I used cursor to get user_ids from AcctUsers table, then dynamically build
> the SQL statment based on the User_ID. It works fine for up to about 40
> user_ids. But it out off the chararter limit for variable nverachar and
> sp_executesql. The way that I choose dynamical SQL because the business
> user may add new user_id to AcctUsers table and then run a report, which
> will call this query.
> Can anyone help me?
> Thanks.
> Perayu
> Here are the DDL:
> CREATE TABLE [dbo].[AcctUsers] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middle_initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Security] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [user_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [screen_id] [smallint] NOT NULL ,
> [security_level] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>

Dynamic SQL calling a function

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

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

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

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

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

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