Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Wednesday, March 21, 2012
dynamically switching databases in a script
I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.
I need the correct syntax, the following won't work:
DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname
OPEN DBS
FETCH NEXT
FROM DBS
INTO
@.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
USE @.dbname
The last line - the "USE" statement - is invalid. The following for example works:
USE master
But when supplied a declared variable a syntax error results for the use command because it expects an identifier.
So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?
Thanks for your time.
This is not possible right now since you cannot use variables in lot of statements in place of options or identifiers. You can use dynamic SQL though and below is the easiest way to do it:
declare @.sp nvarchar(500)
...
while .....
begin
-- use dbo.sp_executesql for SQL Server 2000
set @.sp = quotename(@.dbname) + N'sys.sp_executesql'
exec @.sp N'your sql string that needs to execute against db'
...
end
|||although this allowed the "use .." statement to run, it didn't have the effect I need. The remainder of the script was still running in the context of the original database.|||So here's what I really need:
I need a way to switch the context of a script from one database to another, where I do not know the name of the databases beforehand (so they can't be hard-coded).
|||What I suggested will work provided the code that you want to run within context of the database is executed dynamically. Another approach is to pre-process the script file based on the database name and then run it. With SQL Server 2005, you can do this using SQLCMD pre-processing features.Sunday, March 11, 2012
Dynamically execute stored procedure
Hello,
I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:
insert into Table1
(
id, name
)
select id, name
from Table2
Can you do something like:
exec spProc @.id = id, @.name = name
from Table1
Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.
Thanks.
I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:
insert into Table1
(
id, name
)
select id, name
from Table2
Can you do something like:
exec spProc @.id = id, @.name = name
from Table1
Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.
Thanks.
You can create a SQL cursor base on Table 1. Then iterate over the cursor and build your SQL statements dynamically using that syntax of the Execute statement. You can find syntax in SQL Books Online.
HTH.
Dynamically execute a string as an expression
Hi,
Is it possible to execute a string which is entered in the value of a texbox.
For example:
I have a table with 4 groups. The detail row of the table is filled with a dynamic query like:
="SELECT FactSales.CustomerNr, " & Parameters!SalesFigure.Value & " AS SelectedFigure FROM DWHSales". The "SelectedFigure" comes from a Parameter Combobox. Because the SelectedFigure could not allways be sumed (sometimes I have to do some special math's), i will put in the group header rows a string like
=Code.GenerateSumString(......)
which returns a string like "Sum(Fields!Fieldx.Value)" and this string should be executed, Is there a mechanism like =Execute(Code.GenerateSumString(...)) available or how can I do such things?
Thanks
Hans
Wednesday, March 7, 2012
dynamicall execute SP without dynamic sql
How would you go about dynamically executing a stored proc dependent on
a variable? I cannot use dynamic sql.Why do you want to do without dynamic sql?
a variable? I cannot use dynamic sql.Why do you want to do without dynamic sql?
Madhivanan|||You can use a parameter instead of a proc name (see EXECUTE in Books
Online):
exec @.p
But this is problematic if different procedures may require different
parameters. Or if the number of procedures is relatively small, then
you could just use IF ... ELSE ... to conditionally execute a proc.
Simon|||Use IF statements
IF @.var = 'Proc1'
EXEC Proc1
IF @.var = 'Proc2'
EXEC Proc2
...
If you create a new proc you just need to add it to the list. You could
even generate the list automatically from the info schema ROUTINES
view.
--
David Portas
SQL Server MVP
--|||That sounds like a interesting solution that would probably work. How
would you generate that list and then incorporate it into the if
statements. Thanks!|||Just query against the routine_name column and then cut and paste into
your SP. You still can't expect to automate that process entirely
without using dynamic SQL. Does that matter? Assuming you have adequate
change control procedures in place it shouldn't be a problem.
--
David Portas
SQL Server MVP
--
Sunday, February 26, 2012
Dynamic Update with a sub select
Hi I need some help writing a dynamic Update with a sub select.
I am trying to execute this query and retrieve a variable.The update and select work separately but when I put them together I get the following error,
incorrect syntax near’= ‘
DECLARE @.SQL NVARCHAR(4000)
DECLARE @.ParameterList NVARCHAR(4000)
Declare @.WorkingSheduleID Bigint
SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'
SET @.SQL = 'UPDATEdbo.['+ @.TableName +'] SET UID ='
Set @.SQL = @.SQL + '@.XCustomerID'
Set@.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='
Set@.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'
Set@.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('
Set@.SQL = @.SQL +'@.XDeliveryDate'
Set@.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'
EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT,@.DeliveryDate
Any Help appreciated
Mr Tumnus wrote:
Hi I need some help writing a dynamic Update with a sub select.
I am trying to execute this query and retrieve a variable. The update and select work separately but when I put them together I get the following error,
incorrect syntax near’= ‘
DECLARE @.SQL NVARCHAR(4000)
DECLARE @.ParameterList NVARCHAR(4000)
Declare @.WorkingSheduleID Bigint
SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'
SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='
Set @.SQL = @.SQL + '@.XCustomerID'
Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='
Set @.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'
Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('
Set @.SQL = @.SQL +'@.XDeliveryDate'
Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'
EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate
Any Help appreciated
I think that the indicated (red) bracket is wrong as this is bracketing the SELECT away from (at a different level to) the other parts of the query (FROM, WHERE). I am less certain about which corresponding bracket to remove but I think it is the indicated one (blue).
|||
No change, I still get the same error
I have tried to use @.@.Identity to retrieve the variable, but I keep getting the identity of a query I run earlier in the SP (don’t sure I am using @.@.Identity properly).
I am fairly new to SQL and would appreciate any advice.
|||Have you tried capturing the value of @.SQL and running that interactively with correct surrouding code (the DECLAREs, SETs, and a SELECT to inspect the final value). If you can find a version that works like that then you should only need to build it.
Another option is to split the operation into a batch of 2 steps like:
Code Snippet
SET @.SQL = 'SELECT @.XWorkingSheduleID = Max (WorkingSheduleID)'SET @.SQL = @.SQL +' From dbo.['+ @.TableName +']'
SET @.SQL = @.SQL + ' Where (DeliveryDate = CAST(@.XDeliveryDate'
SET @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0); '
SET @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID = '
SET @.SQL = @.SQL + '@.XCustomerID, SlotClosed = 1'
SET @.SQL = @.SQL +' WHERE (WorkingSheduleID = @.XWorkingSheduleID)'
For your @.SQL setting code.
|||Hi,try this:
Code Snippet
DECLARE @.SQL NVARCHAR(4000)
DECLARE @.TABLENAME VARCHAR(100)
DECLARE @.ParameterList NVARCHAR(4000)
Declare @.WorkingSheduleID Bigint
SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'
SET @.TableName = 'SomeTable'
SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='
Set @.SQL = @.SQL + '@.XCustomerID'
Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = '
Set @.SQL = @.SQL +' (Select Max (WorkingSheduleID)'
Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('
Set @.SQL = @.SQL +'@.XDeliveryDate'
Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'
PRINT @.SQL
UPDATE dbo.[SomeTable]
SET
UID =@.XCustomerID ,
SlotClosed=1
where WorkingSheduleID =
(
Select Max (WorkingSheduleID) From dbo.[SomeTable]
Where DeliveryDate =(CAST(@.XDeliveryDate AS datetime)) And (SlotClosed=0))
)
Don′t know why you did the thing with the @.XcustomerId in the brackets, but you wither leave that out or put it somewhere in there where-clause instead.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Try to change the query as follows :
DECLARE @.SQL NVARCHAR(4000)
DECLARE @.ParameterList NVARCHAR(4000)
Declare @.WorkingSheduleID Bigint
SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'
Set @.SQL = 'Select @.XWorkingSheduleID = Max (WorkingSheduleID)'
Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('
Set @.SQL = @.SQL +'@.XDeliveryDate'
Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0);'
set @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID ='
Set @.SQL = @.SQL + '@.XCustomerID'
Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = @.XWorkingSheduleID '
EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate
SELECT @.XWorkingSheduleID
Friday, February 24, 2012
Dynamic table name from varchar field
Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>
Sunday, February 19, 2012
Dynamic SQL with output values
Hi:
How can I in SQL Server 2000 (using Transact SQL) execute a dynamic sql string and at the same time retrieve output params ?
ThanksPlease give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?|||Originally posted by rnealejr
Please give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?
Here's an simple example:
SELECT @.num_records = COUNT(*)
FROM @.TableName
How can I in SQL Server 2000 (using Transact SQL) execute a dynamic sql string and at the same time retrieve output params ?
ThanksPlease give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?|||Originally posted by rnealejr
Please give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?
Here's an simple example:
SELECT @.num_records = COUNT(*)
FROM @.TableName
Friday, February 17, 2012
Dynamic SQL Issue
When I execute the following stored procedure I get the
error: 'Invalid operator for data type. Operator equals
subtract, type equals varchar.'
SQL Server thinks I'm trying to subtract the mobile_phone
instead of adding dashes between the numbers.
Here is my stored procedure:
---
create PROCEDURE SelectSortedUsers
@.SortColumn varchar(70)
, @.SortDirection char(4)
AS
declare @.sqlstring varchar(2000);
set @.sqlstring = 'select u.last_name
, u.logon , territory
, r.short_description as Role
, r.role_key
, u.active
, CONVERT(varchar,u.last_login_dt,101) as
last_login_dt
, u.email
, substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) as mobile_phone
from users u
inner join roles r
on u.role_key = r.role_key
order by u.' + @.SortColumn + ' ' + @.SortDirection
exec (@.sqlstring);
=========================================================
How can I add dashes for mobile phone?
Thanks.
DarinYou need to surround strings with single quotes (you will probably see the
problem if you use PRINT @.sql instead of EXEC(@.sql))
, ''' + substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Darin Browne" <db@.nospam.com> wrote in message
news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
> When I execute the following stored procedure I get the
> error: 'Invalid operator for data type. Operator equals
> subtract, type equals varchar.'
> SQL Server thinks I'm trying to subtract the mobile_phone
> instead of adding dashes between the numbers.
> Here is my stored procedure:
> ---
> create PROCEDURE SelectSortedUsers
> @.SortColumn varchar(70)
> , @.SortDirection char(4)
> AS
> declare @.sqlstring varchar(2000);
> set @.sqlstring = 'select u.last_name
> , u.logon , territory
> , r.short_description as Role
> , r.role_key
> , u.active
> , CONVERT(varchar,u.last_login_dt,101) as
> last_login_dt
> , u.email
> , substring(u.mobile_phone, 1, 3) + '-' +
> substring(u.mobile_phone, 4, 3) + '-' +
> substring(u.mobile_phone, 7, 4) as mobile_phone
> from users u
> inner join roles r
> on u.role_key = r.role_key
> order by u.' + @.SortColumn + ' ' + @.SortDirection
> exec (@.sqlstring);
> =========================================================> How can I add dashes for mobile phone?
> Thanks.
> Darin|||Aaron, thanks for your quick reply.
Applying your suggestion, I get an error because Server
doesn't know what table 'u' is aliasing because it's now
outside the dynmaic string where 'u' is aliased.
I've tried moving the 3 quotes around to find the perfect
spot but to no avail.
Any ideas?
Thanks.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>|||It's working!
Thanks for your help.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>
error: 'Invalid operator for data type. Operator equals
subtract, type equals varchar.'
SQL Server thinks I'm trying to subtract the mobile_phone
instead of adding dashes between the numbers.
Here is my stored procedure:
---
create PROCEDURE SelectSortedUsers
@.SortColumn varchar(70)
, @.SortDirection char(4)
AS
declare @.sqlstring varchar(2000);
set @.sqlstring = 'select u.last_name
, u.logon , territory
, r.short_description as Role
, r.role_key
, u.active
, CONVERT(varchar,u.last_login_dt,101) as
last_login_dt
, u.email
, substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) as mobile_phone
from users u
inner join roles r
on u.role_key = r.role_key
order by u.' + @.SortColumn + ' ' + @.SortDirection
exec (@.sqlstring);
=========================================================
How can I add dashes for mobile phone?
Thanks.
DarinYou need to surround strings with single quotes (you will probably see the
problem if you use PRINT @.sql instead of EXEC(@.sql))
, ''' + substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Darin Browne" <db@.nospam.com> wrote in message
news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
> When I execute the following stored procedure I get the
> error: 'Invalid operator for data type. Operator equals
> subtract, type equals varchar.'
> SQL Server thinks I'm trying to subtract the mobile_phone
> instead of adding dashes between the numbers.
> Here is my stored procedure:
> ---
> create PROCEDURE SelectSortedUsers
> @.SortColumn varchar(70)
> , @.SortDirection char(4)
> AS
> declare @.sqlstring varchar(2000);
> set @.sqlstring = 'select u.last_name
> , u.logon , territory
> , r.short_description as Role
> , r.role_key
> , u.active
> , CONVERT(varchar,u.last_login_dt,101) as
> last_login_dt
> , u.email
> , substring(u.mobile_phone, 1, 3) + '-' +
> substring(u.mobile_phone, 4, 3) + '-' +
> substring(u.mobile_phone, 7, 4) as mobile_phone
> from users u
> inner join roles r
> on u.role_key = r.role_key
> order by u.' + @.SortColumn + ' ' + @.SortDirection
> exec (@.sqlstring);
> =========================================================> How can I add dashes for mobile phone?
> Thanks.
> Darin|||Aaron, thanks for your quick reply.
Applying your suggestion, I get an error because Server
doesn't know what table 'u' is aliasing because it's now
outside the dynmaic string where 'u' is aliased.
I've tried moving the 3 quotes around to find the perfect
spot but to no avail.
Any ideas?
Thanks.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>|||It's working!
Thanks for your help.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>
Dynamic SQL is faster.
Hi I have a stored proc -
takes a long time to execute so re-compilation isn't an issue.
I pass a comma separated varchar param to the stored procedure.
exec sp_somename @.CSV = '1,2,3'
If I build the SQL statement in the SP and then execute the statement
dynamically:
EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4) ...rest
of sql'
as opposed to this,
having populated a temp table #t with @.CSV values.
SELECT * FROM
table1, #t, ... other tables
WHERE
table1.ID = #t.ID
...rest of sql
I used a function and SP to populate the #t; all took negliable speed.
The dynamic SQL performs twice as fast as the temp table/variable method.
Surprised I thought SELECT IN (.....) was converted into a join and would
be just as fast
Don't like Dynamic SQL if I can avoid it.Do you have any indexes on the temp table? if not, would one help on the
joined column?
David Gugick
Imceda Software
www.imceda.com
"Yitzak" <terryshamir@.bob.com> wrote in message
news:prG2e.903$VN1.310@.newsfe1-win.ntli.net...
> Hi I have a stored proc -
> takes a long time to execute so re-compilation isn't an issue.
> I pass a comma separated varchar param to the stored procedure.
> exec sp_somename @.CSV = '1,2,3'
>
> If I build the SQL statement in the SP and then execute the statement
> dynamically:
> EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4)
> ...rest
> of sql'
> as opposed to this,
> having populated a temp table #t with @.CSV values.
> SELECT * FROM
> table1, #t, ... other tables
> WHERE
> table1.ID = #t.ID
> ...rest of sql
> I used a function and SP to populate the #t; all took negliable speed.
> The dynamic SQL performs twice as fast as the temp table/variable method.
> Surprised I thought SELECT IN (.....) was converted into a join and would
> be just as fast
> Don't like Dynamic SQL if I can avoid it.
>
>|||I thought because the table was so small at most having 10 rows - they would
be no benefit but I'll give it a go.
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#DuCivYNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> Do you have any indexes on the temp table? if not, would one help on the
> joined column?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
> "Yitzak" <terryshamir@.bob.com> wrote in message
> news:prG2e.903$VN1.310@.newsfe1-win.ntli.net...
method.
would
>|||Put some indexes on and did improve performance.
Changed some other stored procedures but still Dynamic SQL wins out. Thats
not to mention its clear advantage when you can easily optimise for no ids
e.g. @.param = '' by not including the IN statement in the SQL string
executed.
Just don't like Dynamic SQL - sp_depnds wont work. All the security issues.
but looks like I gotta use it.|||>> I pass a comma separated varchar param to the stored procedure. <<
Ever hear about First Normal Form (1NF)?
Pardon the fact that this "cut & paste" is in Standard SQL and not
dialect. Passing a list of parmeters to a stored procedure can be done
by putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
..
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the real query, in SQL-92 syntax:
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.
You can then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
Of course the right way to do this would be with a base table that
holds the list, or a longer parameter list:
WHERE x IN (@.p1, COALESCE (@.p2, @.p1), .. COALESCE (@.p99, @.p1);
The reason for the Coalesce() is to guarantee you have no nulls. You
must have a value for @.p1. All the other partameters default to NULL.
Alternatively, you can have a local variable, @.p0, which is set to some
impossible value as a sentinel.|||Actually I executed
set @.param = '1,2,3'
insert into #t
exec('SELECT ' + REPLACE(@.param, ',' ' UNION ALL SELECT ') )
To give me
insert into #t
exec (select 1 union all select 2 union all select 3)
Each row produced by executing this is one row and column of the temp table.
Is that what you meant by 1NF (every field must be atomic?)
Must of explained myself badly.
Point is using this CSV param dynamically in a SP is much faster than
breaking CSV param down into a temp table/variable or using a table
returning function.
SP recompile time is not an issue. Maybe if the CSV param gets very large
then using the temp table or function may be quicker.
Point is don't like dynamic SQL but this time forced to.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112318927.848861.167290@.g14g2000cwa.googlegroups.com...
> Ever hear about First Normal Form (1NF)?
> Pardon the fact that this "cut & paste" is in Standard SQL and not
> dialect. Passing a list of parmeters to a stored procedure can be done
> by putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> ...
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the real query, in SQL-92 syntax:
>
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma.
> You can then write:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
> Of course the right way to do this would be with a base table that
> holds the list, or a longer parameter list:
> WHERE x IN (@.p1, COALESCE (@.p2, @.p1), .. COALESCE (@.p99, @.p1);
> The reason for the Coalesce() is to guarantee you have no nulls. You
> must have a value for @.p1. All the other partameters default to NULL.
> Alternatively, you can have a local variable, @.p0, which is set to some
> impossible value as a sentinel.
>|||Yitzak wrote:
> Hi I have a stored proc -
> takes a long time to execute so re-compilation isn't an issue.
> I pass a comma separated varchar param to the stored procedure.
> exec sp_somename @.CSV = '1,2,3'
>
> If I build the SQL statement in the SP and then execute the statement
> dynamically:
> EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4)
> ...rest of sql'
>
You have several options, which are explained at
http://www.sommarskog.se/arrays-in-sql.html
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks
Tried different options all better than dynamic SQL (maintenance,
debugging, security) - essentially all use a table to join.
This in my cases is always double the speed of the similar Dynamic SQL.
Recompilation ain't an issue on a SP that takes 40secs to run..
Can't understand as I thought MSSQL turned a "Select IN" into a join on temp
table, but creating my own indexed temp table/variable from SP or Function
takes twice teh time of "SELECT IN"
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:#EOF$xwNFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Yitzak wrote:
> You have several options, which are explained at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
takes a long time to execute so re-compilation isn't an issue.
I pass a comma separated varchar param to the stored procedure.
exec sp_somename @.CSV = '1,2,3'
If I build the SQL statement in the SP and then execute the statement
dynamically:
EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4) ...rest
of sql'
as opposed to this,
having populated a temp table #t with @.CSV values.
SELECT * FROM
table1, #t, ... other tables
WHERE
table1.ID = #t.ID
...rest of sql
I used a function and SP to populate the #t; all took negliable speed.
The dynamic SQL performs twice as fast as the temp table/variable method.
Surprised I thought SELECT IN (.....) was converted into a join and would
be just as fast
Don't like Dynamic SQL if I can avoid it.Do you have any indexes on the temp table? if not, would one help on the
joined column?
David Gugick
Imceda Software
www.imceda.com
"Yitzak" <terryshamir@.bob.com> wrote in message
news:prG2e.903$VN1.310@.newsfe1-win.ntli.net...
> Hi I have a stored proc -
> takes a long time to execute so re-compilation isn't an issue.
> I pass a comma separated varchar param to the stored procedure.
> exec sp_somename @.CSV = '1,2,3'
>
> If I build the SQL statement in the SP and then execute the statement
> dynamically:
> EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4)
> ...rest
> of sql'
> as opposed to this,
> having populated a temp table #t with @.CSV values.
> SELECT * FROM
> table1, #t, ... other tables
> WHERE
> table1.ID = #t.ID
> ...rest of sql
> I used a function and SP to populate the #t; all took negliable speed.
> The dynamic SQL performs twice as fast as the temp table/variable method.
> Surprised I thought SELECT IN (.....) was converted into a join and would
> be just as fast
> Don't like Dynamic SQL if I can avoid it.
>
>|||I thought because the table was so small at most having 10 rows - they would
be no benefit but I'll give it a go.
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#DuCivYNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> Do you have any indexes on the temp table? if not, would one help on the
> joined column?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
> "Yitzak" <terryshamir@.bob.com> wrote in message
> news:prG2e.903$VN1.310@.newsfe1-win.ntli.net...
method.
would
>|||Put some indexes on and did improve performance.
Changed some other stored procedures but still Dynamic SQL wins out. Thats
not to mention its clear advantage when you can easily optimise for no ids
e.g. @.param = '' by not including the IN statement in the SQL string
executed.
Just don't like Dynamic SQL - sp_depnds wont work. All the security issues.
but looks like I gotta use it.|||>> I pass a comma separated varchar param to the stored procedure. <<
Ever hear about First Normal Form (1NF)?
Pardon the fact that this "cut & paste" is in Standard SQL and not
dialect. Passing a list of parmeters to a stored procedure can be done
by putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
..
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the real query, in SQL-92 syntax:
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.
You can then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
Of course the right way to do this would be with a base table that
holds the list, or a longer parameter list:
WHERE x IN (@.p1, COALESCE (@.p2, @.p1), .. COALESCE (@.p99, @.p1);
The reason for the Coalesce() is to guarantee you have no nulls. You
must have a value for @.p1. All the other partameters default to NULL.
Alternatively, you can have a local variable, @.p0, which is set to some
impossible value as a sentinel.|||Actually I executed
set @.param = '1,2,3'
insert into #t
exec('SELECT ' + REPLACE(@.param, ',' ' UNION ALL SELECT ') )
To give me
insert into #t
exec (select 1 union all select 2 union all select 3)
Each row produced by executing this is one row and column of the temp table.
Is that what you meant by 1NF (every field must be atomic?)
Must of explained myself badly.
Point is using this CSV param dynamically in a SP is much faster than
breaking CSV param down into a temp table/variable or using a table
returning function.
SP recompile time is not an issue. Maybe if the CSV param gets very large
then using the temp table or function may be quicker.
Point is don't like dynamic SQL but this time forced to.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112318927.848861.167290@.g14g2000cwa.googlegroups.com...
> Ever hear about First Normal Form (1NF)?
> Pardon the fact that this "cut & paste" is in Standard SQL and not
> dialect. Passing a list of parmeters to a stored procedure can be done
> by putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> ...
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the real query, in SQL-92 syntax:
>
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma.
> You can then write:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
> Of course the right way to do this would be with a base table that
> holds the list, or a longer parameter list:
> WHERE x IN (@.p1, COALESCE (@.p2, @.p1), .. COALESCE (@.p99, @.p1);
> The reason for the Coalesce() is to guarantee you have no nulls. You
> must have a value for @.p1. All the other partameters default to NULL.
> Alternatively, you can have a local variable, @.p0, which is set to some
> impossible value as a sentinel.
>|||Yitzak wrote:
> Hi I have a stored proc -
> takes a long time to execute so re-compilation isn't an issue.
> I pass a comma separated varchar param to the stored procedure.
> exec sp_somename @.CSV = '1,2,3'
>
> If I build the SQL statement in the SP and then execute the statement
> dynamically:
> EXEC ('SELECT * FROM table1, ... other tables WHERE ID IN (1,2,3,4)
> ...rest of sql'
>
You have several options, which are explained at
http://www.sommarskog.se/arrays-in-sql.html
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks
Tried different options all better than dynamic SQL (maintenance,
debugging, security) - essentially all use a table to join.
This in my cases is always double the speed of the similar Dynamic SQL.
Recompilation ain't an issue on a SP that takes 40secs to run..
Can't understand as I thought MSSQL turned a "Select IN" into a join on temp
table, but creating my own indexed temp table/variable from SP or Function
takes twice teh time of "SELECT IN"
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:#EOF$xwNFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Yitzak wrote:
> You have several options, which are explained at
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
Wednesday, February 15, 2012
Dynamic SQL in UDF
Does anyone know how to execute dynamic SQL in a user defined function? Here's a quick example of what I would like to do. I get an error that only functions and extended stored procedures may be called in a function. Is there any other way to execute dynamic SQL in a UDF?
CREATE function dbo.test(@.table char(40), @.value char(40))
RETURNS int
AS
BEGIN
DECLARE @.return char(3)
DECLARE @.sqlstring nvarchar(500)
SET @.sqlstring = 'Select count(*) From @.table Where id = @.value'
Execute sp_executesql @.sqlstring
RETURN(@.return)
ENDHow would you use this function?
SELECT dbo.Test('a','b')
?
Why not just do
EXEC @.rc = Test 'a','b'|||I'm not sure I follow your reply. I want to call the UDF from a table constraint.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.
UDF's do not support dynamic sql. You would have to go with a stored procedure in order to gain that flexibility. I think they have that somewhere on msdn too..|||I can't find the msdn outline... here's a limitation rundown from informit (http://www.informit.com/isapi/product_id~{0D83BA18-CDB2-4D74-9C2A-AA44581B27B9}/element_id~{43287535-9508-43D6-BB36-4852A7A9F91B}/st~{340C91CD-6221-4982-8F32-4A0A9A8CF080}/session_id~{65960459-451E-4EF6-9D3B-7E7CF0E4CB0B}/content/articlex.asp) that I found to be pretty comprehensive.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.
A CONSTRAINT? What would that do?
what are you trying to restrict?|||I'm trying to restrict date ranges from overlapping in several tables. I was hoping to use dynamic sql in a udf so that the udf can be reused by multiple tables.
Here's an example: A salesman can be licensed in a particular state to sell widgets. His licensed can be terminated and re-instated in a state. However, he can't hold two licenses in the same state at the same time. Our system must track each instance of a license the salesman has had in every state. Our developers didn't think validating overlapping start and end dates was important so no validation exists in the java code. Hence we have bad data with overlapping date ranges for a salesman in a state.
**Table Structure (not syntactically correct):
id int(pk identity)
salesman_id int
state_id int
start_date datetime
end_date datetime
I would like the table constraint to call the udf which would return if there were any date ranges overlapping the inserted or updated data. If so prevent the insert or update. Oh yeah, this would prevent me from having to code a trigger on each table this logic will be used.
Hope this helps!|||Why not just create a unique index on saleman id, State?
use an update trigger to move to current row to a history table where it's not unique?
The create a view if the need to see all of the data.
If someone tries to add another salesman that's already in the same state, they'll get an exception...
What they probably should be doing is an update not an insert anyway
MOO|||Yeah, that would work but I would have to do that for every table where this occurs. I was looking for an 'easier' solution that may end up being just as complex. I would prefer not to have to maintain seperate history tables nor triggers on each table but thanks for the ideas.
CREATE function dbo.test(@.table char(40), @.value char(40))
RETURNS int
AS
BEGIN
DECLARE @.return char(3)
DECLARE @.sqlstring nvarchar(500)
SET @.sqlstring = 'Select count(*) From @.table Where id = @.value'
Execute sp_executesql @.sqlstring
RETURN(@.return)
ENDHow would you use this function?
SELECT dbo.Test('a','b')
?
Why not just do
EXEC @.rc = Test 'a','b'|||I'm not sure I follow your reply. I want to call the UDF from a table constraint.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.
UDF's do not support dynamic sql. You would have to go with a stored procedure in order to gain that flexibility. I think they have that somewhere on msdn too..|||I can't find the msdn outline... here's a limitation rundown from informit (http://www.informit.com/isapi/product_id~{0D83BA18-CDB2-4D74-9C2A-AA44581B27B9}/element_id~{43287535-9508-43D6-BB36-4852A7A9F91B}/st~{340C91CD-6221-4982-8F32-4A0A9A8CF080}/session_id~{65960459-451E-4EF6-9D3B-7E7CF0E4CB0B}/content/articlex.asp) that I found to be pretty comprehensive.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.
A CONSTRAINT? What would that do?
what are you trying to restrict?|||I'm trying to restrict date ranges from overlapping in several tables. I was hoping to use dynamic sql in a udf so that the udf can be reused by multiple tables.
Here's an example: A salesman can be licensed in a particular state to sell widgets. His licensed can be terminated and re-instated in a state. However, he can't hold two licenses in the same state at the same time. Our system must track each instance of a license the salesman has had in every state. Our developers didn't think validating overlapping start and end dates was important so no validation exists in the java code. Hence we have bad data with overlapping date ranges for a salesman in a state.
**Table Structure (not syntactically correct):
id int(pk identity)
salesman_id int
state_id int
start_date datetime
end_date datetime
I would like the table constraint to call the udf which would return if there were any date ranges overlapping the inserted or updated data. If so prevent the insert or update. Oh yeah, this would prevent me from having to code a trigger on each table this logic will be used.
Hope this helps!|||Why not just create a unique index on saleman id, State?
use an update trigger to move to current row to a history table where it's not unique?
The create a view if the need to see all of the data.
If someone tries to add another salesman that's already in the same state, they'll get an exception...
What they probably should be doing is an update not an insert anyway
MOO|||Yeah, that would work but I would have to do that for every table where this occurs. I was looking for an 'easier' solution that may end up being just as complex. I would prefer not to have to maintain seperate history tables nor triggers on each table but thanks for the ideas.
Dynamic SQL in Cursor
Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'
Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegro ups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>
|||Thanks, I tried that.
|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegro ups.com...
> Thanks, I tried that.
>
|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help
|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegr oups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>
|||I don't really understand how to use what you sent me for my code.
|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegr oups.com...
>I don't really understand how to use what you sent me for my code.
>
|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov
|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'
Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegro ups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>
|||Thanks, I tried that.
|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegro ups.com...
> Thanks, I tried that.
>
|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help
|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegr oups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>
|||I don't really understand how to use what you sent me for my code.
|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegr oups.com...
>I don't really understand how to use what you sent me for my code.
>
|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov
|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.
Dynamic SQL in Cursor
Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>|||Thanks, I tried that.|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegroups.com...
> Thanks, I tried that.
>|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegroups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>|||I don't really understand how to use what you sent me for my code.|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegroups.com...
>I don't really understand how to use what you sent me for my code.
>|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.|||As already suggested, try accomplish this in a set based manner. It will in most cases produce less
code that is easier to maintain and in vast majority oc cases significantly better preformance.
Anyhow, what you'd need to do is to EXEC the whole DECLARE statement as a string:
DECLARE @.sql nvarchar(2000)
SET @.sql = 'DECLARE c CURSOR FOR SELECT ...'
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<mosheallen@.gmail.com> wrote in message news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>|||Thanks, I tried that.|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegroups.com...
> Thanks, I tried that.
>|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegroups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>|||I don't really understand how to use what you sent me for my code.|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegroups.com...
>I don't really understand how to use what you sent me for my code.
>|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.|||As already suggested, try accomplish this in a set based manner. It will in most cases produce less
code that is easier to maintain and in vast majority oc cases significantly better preformance.
Anyhow, what you'd need to do is to EXEC the whole DECLARE statement as a string:
DECLARE @.sql nvarchar(2000)
SET @.sql = 'DECLARE c CURSOR FOR SELECT ...'
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<mosheallen@.gmail.com> wrote in message news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>
Dynamic SQL in Cursor
Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>|||Thanks, I tried that.|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegroups.com...
> Thanks, I tried that.
>|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegroups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>|||I don't really understand how to use what you sent me for my code.|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegroups.com...
>I don't really understand how to use what you sent me for my code.
>|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>|||Thanks, I tried that.|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegroups.com...
> Thanks, I tried that.
>|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegroups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>|||I don't really understand how to use what you sent me for my code.|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegroups.com...
>I don't really understand how to use what you sent me for my code.
>|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.
Dynamic SQL in a CTE?
Hi all,
Is it possible to execute dynamic SQL in a CTE? that is create the dynamic SQL, stick it into a variable 'strCriteria' and then execute 'strCriteria' within the CTE?
WITH UPCTE
AS
(
this doesn't work
Exec sp_sqlexec @.strCriteria
SELECT DISTINCT Comp_Name FROM UPCTE;
Meltdown:
Will something like this get you through as a workaround?
declare @.baseCTEDefinition varchar (500)
declare @.theSelectStatement varchar (500)set @.baseCTEDefinition = 'with myCTE as ( select 1 as singlet ) '
set @.theSelectStatement = 'select * from myCTE 'exec ( baseCTEDefinition + @.theSelectStatement )
|||Can you please explain why you need to use dynamic SQL within CTE? CTE is similar to a view, it is a declarative construct, the definition of the CTE is expanded in all references in the query, compiled, optimized and executed. So you cannot execute dynamic SQL or call SPs directly from CTE. If you just want to execute a SQL statement dynamically then use EXEC or sp_executesql. Also, sp_sqlexec has been deprecated for a while and it will be removed soon from the product. So you should remove those from your code also. Lastly, why do you need to use dynamic SQL and what are you trying to solve that requires use of dynamic SQL. If you can explain your problem it will be helpful to suggest easier solutions.Dave
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'
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>>
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'
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>>
Subscribe to:
Posts (Atom)