Dynamic SQL Command: defined

Showing posts with label defined. Show all posts

Tuesday, March 27, 2012

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scripts/article.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||Like most people you need proper database change control
then these issues would disappear. It seems that people
are all to quick to think that their processes are OK
until the inevitable happens and they are in the _hit!
Smart people use DB Ghost.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL
Server
>--Original Message--
>MS SQL Server 2000 does not allow columns with Default
value constraints to
>be dropped. We are looking for an easy way to drop such
columns. The problem
>occurs because the DEFAULT clause actually creates a
constraint on the
>table. This name of this constraint is chosen by SQL
Server and will differ
>from database to database.
>The seemingly obvious solution:
>exec sp_unbindefault 'table.column'
>does not work but gives us the following error:
>Server: Msg 15049, Level 11, State 1, Procedure
sp_unbindefault, Line 98
>Cannot unbind from 'table.column'. Use ALTER TABLE DROP
CONSTRAINT
>OK, ALTER TABLE DROP CONSTRAINT would certainly work,
but we don't know the
>name of the contraint! Makes it kind of hard for us to
script this to work
>for multiple databases.
>The only working solution we have seems very cumbersome.
(I guess we could
>convert it to a stored procedure.)
>DECLARE @.STR VARCHAR(100)
>SET @.STR = (
>SELECT NAME
>FROM SYSOBJECTS SO
>JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
>WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
>AND SO.XTYPE = 'D' AND SC.COLID =>(SELECT COLID FROM SYSCOLUMNS
>WHERE ID = OBJECT_ID('<table>')
>AND NAME = '<column>')
>)
>SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR
EXEC (@.STR)
>The other solution would be to stop using the DEFAULT
clause on the column
>definition, but rather to create a named constraint. But
completely
>eschewing the DEFAULT clause will drive the complexity
of our database
>definition scripts way up. We use DEFAULT values rather
heavily and I don't
>want to have to specify an ALTER TABLE ADD CONSTRAINT
for every column with
>a default value! I don't like this option.
>There's got to be a better way (I hope). I can't believe
it has to be this
>difficult to drop a column simply because it has a
default value specified
>for it. Is there an easy way to deal with this?
>Thanks for your help!
>Joe Geretz
>
>.
>sql

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe Geretz
Check this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scrip...le.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>
|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scri...cle.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Friday, February 17, 2012

Dynamic SQL into a cursor

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

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

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

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

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

declare archivecur cursor
for
@.sql

open archivecur

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

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

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

close archivecur

deallocate archivecur

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

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

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

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

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

execute(@.sql)

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

open archivecur

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

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

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

close archivecur

deallocate archivecur

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

print @.funcreturn
|||

Lamffy,

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

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

Example:

Code Snippet

use northwind

declare @.sql nvarchar(4000)

declare @.customerid nchar(5)

declare @.c cursor

declare @.orderid int

set @.sql = N'

set @.c = cursor local fast_forward

for

select orderid

from dbo.orders

where customerid = @.customerid;

open @.c'

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

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

begin

while 1 = 1

begin

fetch next from @.c into @.orderid

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

print @.orderid

end

close @.c

deallocate @.c

end

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

AMB

|||

Try

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

exec (@.sql)

open archivecur

|||

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

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

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

Adamus

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

Wednesday, February 15, 2012

Dynamic SQL in UDF

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

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

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

Execute sp_executesql @.sqlstring

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

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

?

Why not just do

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

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

A CONSTRAINT? What would that do?

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

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

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

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

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

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

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

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

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

Dynamic SQL calling a function

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

Tuesday, March 27, 2012

Easy way to drop a column with Default Value defined?

Easy way to drop a column with Default Value defined?

Easy way to drop a column with Default Value defined?

Friday, February 17, 2012

Dynamic SQL into a cursor

Wednesday, February 15, 2012

Dynamic SQL in UDF

Dynamic SQL calling a function

Dynamic SQL calling a function

Dynamic SQL Command

Blog Archive

About Me