Is It Possible to use Dynamic Query Inside a func

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is It Possible to use Dynamic Query Inside a func

Author

Topic

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-06-17 : 00:49:43

Hi to all,

Is It possible to use dynamic qyery inside a function in sql server.

For Example:

Create function fn_Test
Returns Table
As
Return sp_ExecuteSql 'SELECT * FROM EMP'

Like this.

With regards
Amjath

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 01:11:28

No

CODO ERGO SUM

Amjath
Yak Posting Veteran

66 Posts

Posted - 2006-06-17 : 02:24:09

Whats the reason???

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 12:52:20

Dymanic SQL cannot be used in a function.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-17 : 19:51:42

Non-deterministic Functions or Stored Procedures calls are not allowed from UDF

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-06-18 : 11:41:24

KH, actually non-deterministic functions are allowed in UDFs. EXECing is not allowed in UDFs. That's why you can't have SPs and dynamic sql in UDFs. The only way to "call" them is with "EXEC".

Perhaps you are thinking about indexing a computed column based on a UDF. To index the column, (among other things) the underlying function must be deterministic.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-18 : 13:48:07

Actually, you can execute stored procedures inside a function, as long as they are extended stored procedures.

Except that you cannot execute the extended stored procedure sp_executesql, because dynamic SQL is not allowed in functions.


drop function dbo.F_TEMP
go
create function dbo.F_TEMP ( )
returns int
as
begin
declare @out int
execute sp_executesql N'select @cnt = 1',N'@cnt int  output',@cnt = @out output
return @out
end
go
print 'Try function dbo.F_TEMP ( ) with sp_executesql'
select dbo.F_TEMP ( )

Results:


Try function dbo.F_TEMP ( ) with sp_executesql
Server: Msg 557, Level 16, State 2, Procedure F_TEMP, Line 6
Only functions and extended stored procedures can be executed from within a function.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-18 : 23:26:19

quote:
Originally posted by TG

KH, actually non-deterministic functions are allowed in UDFs. EXECing is not allowed in UDFs. That's why you can't have SPs and dynamic sql in UDFs. The only way to "call" them is with "EXEC".

Perhaps you are thinking about indexing a computed column based on a UDF. To index the column, (among other things) the underlying function must be deterministic.

Be One with the Optimizer
TG

TG, thanks for the correction

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-19 : 01:49:29

quote:
Originally posted by Amjath

Whats the reason???

One of the limitations of Functions.
Can you explain what you are trying to do?
Read more about Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

gongxia649
So Suave

344 Posts

Posted - 2006-06-23 : 13:08:39

have something shorter to read? that website is endless

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 01:44:39

quote:
Originally posted by gongxia649

have something shorter to read? that website is endless

You Should read it fully to understand Dynamic SQL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources