473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

sp_executesql

Hi all,
Can sp_executesql used inside a user defined function, i
tried but it has compiled well, but when i call the functio it shows
Only functions and extended stored procedures can be executed from
within a function.

What i have went wrong

Thanks in advance

thomson

Jul 23 '05 #1
3 7403
Unfortunately, it means exactly what it sais - that you cannot execute
anything from within a UDF except an *extended* stored procedure or
another UDF.

Since sp_executesql is not an extended stored procedure it will not
work. You will need to execute your sp_executesql statement within a
stored procedure instead. Good luck!

Dadou.

thomson wrote:
Hi all,
Can sp_executesql used inside a user defined function, i
tried but it has compiled well, but when i call the functio it shows
Only functions and extended stored procedures can be executed from
within a function.

What i have went wrong

Thanks in advance

thomson


Jul 23 '05 #2
thomson (sa**********@yahoo.com) writes:
Can sp_executesql used inside a user defined function, i
tried but it has compiled well, but when i call the functio it shows
Only functions and extended stored procedures can be executed from
within a function.

What i have went wrong


It's important to understand that user-defined functions are designed
from the presumption that they don't alter the state of the database.
Say that you have:

SELECT * FROM tbl WHERE col = dbo.udf()

And dbo.udf() would change the values in tbl.col. How would this affect
the result?

For this reason, you are not permitted to invoke anything from which you
theoretically can alter the database state, and that includes dynamic SQL.
(There are actually some loopholes, but obviously you would be doing
something unsupported and unpredictable if you tried it.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Thank You Very Much For the Detailed Explanation

Regards

thomson

Erland Sommarskog wrote:
thomson (sa**********@yahoo.com) writes:
Can sp_executesql used inside a user defined function, i
tried but it has compiled well, but when i call the functio it shows Only functions and extended stored procedures can be executed from
within a function.

What i have went wrong
It's important to understand that user-defined functions are designed
from the presumption that they don't alter the state of the database.
Say that you have:

SELECT * FROM tbl WHERE col = dbo.udf()

And dbo.udf() would change the values in tbl.col. How would this

affect the result?

For this reason, you are not permitted to invoke anything from which you theoretically can alter the database state, and that includes dynamic SQL. (There are actually some loopholes, but obviously you would be doing
something unsupported and unpredictable if you tried it.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: cliverama | last post by:
help! fried brains.... asp calling a sqlserver7 stored proc which dynamically builds a sqlstatement & passes it to sp_executesql asp page gives the operation not allowed when object is closed...
2
by: Ado | last post by:
I have a full sql statement which was generated dynamicly, and need to execute that string and then take the output and generate a spreadsheet document based on the output. I'm new to sql and the...
1
by: Justin Wong | last post by:
CREATE PROCEDURE dbo.Synchronization_GetNewRecords ( @item varchar(50), @last datetime ) AS SET NOCOUNT ON
1
by: LineVoltageHalogen | last post by:
Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects. Background: 1.) Simple Application that uses sql server...
7
by: LineVoltageHalogen | last post by:
Greetings All, I have a very large query that uses dynamic sql. The sql is very large and it requires it to be broken into three components to avoid the nvarchar(4000) issue: SET @v_SqlString(...
3
by: elRoyFlynn | last post by:
This is a odd problem where a bad plan was chosen again and again, but then not. Using the profiler, I identified an application-issued statement that performed poorly. It took this form: ...
1
by: Matik | last post by:
Hi to all, Probably I'm just doing something stupid, but I would like you to tell me that (if it is so), and point the solution. There ist the thing: I' having a sp, where I call other sp...
1
by: satishchandrat | last post by:
Hi, This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure. I have my SQL string exeeding more than 4000...
5
by: Yash | last post by:
Hi, I am using SQL 2000 SP4. I have compared 2 scenarios: Scenario 1: insert into #bacs_report SELECT ..... WHERE <conditions>
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.