I can't Use sp_executesql within Functions

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

I can't Use sp_executesql within Functions

Author

Topic

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-04-09 : 10:49:59

Hi all
i have Function and in the context of this function i need to build a Dynamic Query String according to input parameters and execute it with sp_executesql. BUT until now i didn't know that SQL doesn't allow to have Exec command within a function,am i right?
Apparently this is true because for example create the following Function..

Create Function Test(@Input int)
Returns int
AS
Begin
Exec sp_who -- only for Test purpose
Return @Input
End

Now Execute this --> Select dbo.test(12).....
Sql Server will return the following Error

Server: Msg 557, Level 16, State 2, Procedure Test, Line 6
Only functions and extended stored procedures can be executed from within a function.

Could Any one help me? i need function with dynamic Sql execution because i can only use function in SELECT statements !!!

Any help greatly would be appreciated.
Kind Regards.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 10:51:41

Yes. you can't. Use stored procedure instead, or use IF ... ELSE block in your function

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 10:51:55

Simple answer is....YOU CAN'T USE D-SQL WITHIN FUNCTIONS, USE STORED PROCEDURES INSTEAD.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-04-09 : 10:55:53

quote:
Originally posted by khtan

Yes. you can't. Use stored procedure instead, or use IF ... ELSE block in your function

KH

Hi Thanks for yoy reply.
But i want to use this function in a SELECT Statement so how can i do the same with stored procedures?
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-09 : 11:00:09

you can't.
what are you trying to acctually do?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 11:04:08

"But i want to use this function in a SELECT Statement so how can i do the same with stored procedures?"
Doesn't sounds like a good idea. Can you accomplish this using stored procedure ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources