473,889 Members | 1,716 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

howto: calling sp_executesql from a function ?

I want to execute a dynamically generated sql-statement
from inside an user-defined-function. Calling functions and
extended stored-procs is allowed so I tried sp_executesql
as well as sp_prepare/sp_execute ....

but both fail with an error 'only functions and extended stored-procs
may be called from inside a function.'

any idea where I might be wrong ?

thx in advance,
Joerg
--
*************** *************** *************** *************** *
Joerg Clausmeyer
Medizinische Informatik und Datenmanagement

CHARITE - Universitätsmed izin Berlin
*************** *************** *************** *************** *
Jul 20 '05 #1
3 41089
Clausmeyer (jc*@charite.de ) writes:
I want to execute a dynamically generated sql-statement
from inside an user-defined-function. Calling functions and
extended stored-procs is allowed so I tried sp_executesql
as well as sp_prepare/sp_execute ....

but both fail with an error 'only functions and extended stored-procs
may be called from inside a function.'

any idea where I might be wrong ?


You cannot use dynamic SQL from a function, neither can you call
stored procedures.

Functions in SQL Server are designed from the perspective that may not
change state in the database, therefore you are quite restricted in
what you can do.

If you post more about your actual business problem, you may get
suggestions on how to address it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
You cannot use dynamic SQL from a function, neither can you call
stored procedures.
thanks for replying,
I'm a bit confused now. Both my docs and the errormessage I get
tell me, that it is allowed to call functions and extended procs from
a function.
sp_executesql is an extended proc, or am I wrong?

If you post more about your actual business problem, you may get
suggestions on how to address it.


the function itself is called from a rather complex select-statement.
It shall take 2 params identifying row and column of a fixed table,
build a select-statement and return the selected value.
simplified code below:
CREATE FUNCTION dbo.udf_GetData Value
(
@RowId int,
@Column varchar(50)
)
RETURNS varchar(50)
AS
BEGIN

declare @sql nvarchar(255)
declare @value varchar(50)

select @sql = 'select ' + @Column + ' from t_OPs where [Id] = ' +
convert(varchar (20), @RowId)
exec [master].dbo.sp_execute sql @sql

RETURN
(@value)

END
--
*************** *************** *************** *************** *
Joerg Clausmeyer
Medizinische Informatik und Datenmanagement

CHARITE - Universitätsmed izin Berlin
*************** *************** *************** *************** *
Jul 20 '05 #3
Clausmeyer (jc*@charite.de ) writes:
thanks for replying,
I'm a bit confused now. Both my docs and the errormessage I get
tell me, that it is allowed to call functions and extended procs from
a function.
sp_executesql is an extended proc, or am I wrong?
Maybe it is. It is still not permitted, and for a very good reason.
the function itself is called from a rather complex select-statement.
It shall take 2 params identifying row and column of a fixed table,
build a select-statement and return the selected value.
simplified code below:


And if your dynamic SQL had performed updates or deletes on the
tables involved in the querey, what had you expected to happen?

As for the problem, what's wrong with:

SELECT @retval = CASE @column
WHEN 'col1' THEN col1
WHEN 'col2' THEN col2
...
END
FROM tbl
WHERE rowid = @rowid

However, I like to add a few more comments.

1) Beware that UDF can severly affect your performance, since the query
might be in practice be converted to a cursor begind the scenes.

2) The fact that want to do such a thing, indicates that your data
model is flawed. Maybe the column names you pass into the query
should have been key values in a table with a two-column key (@rowid,
@column)

3) Beware that if an error occurs in a user-defined function, there is
no way to catch it. The statement that calls the UDF will be terminated,
but @@error will be 0, so you don't if things well or not. Had your
dynamic SQL solution been possible, and you had feed it a invalid
column name, you would not have known.

4) 'select ' + @Column + ' from t_OPs where [Id] = ' +
convert(varchar (20), @RowId) is better written:

SELECT @sql = 'select ' + @Column + ' from t_OPs where [Id] =
convert(varchar (20), @RowId)
EXEC sp_executesql @sql, N'int @RowId', @RowId = @RowId
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

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

Similar topics

2
2406
by: Satish Chimakurthi | last post by:
Hi all, My question is surely a basic one, but somehow, I am not able to figure it out. I have a python file "satish.py" as follows: *satish.py* def main(): y()
3
2396
by: Ken | last post by:
hello, I would to know if it is possible to call an object in a function within a class. Meaning , In a class, A function X calling onto a function Y, and function Y we want one of the two calculation ( eg seconds , out of seconds and minutes) thanks , Ken
13
6723
by: ukrbend | last post by:
I'm new to Javascript and to html and am trying to make the following code snippet work but it doesn't. It refuses to call the getPage() function and I always get a 404 error. I know the code is getting as far as the iframe section as other parts of the html code work correctly. But it is not executing any code within the getPage() function as some "hello world" writes within getPage() wouldn't print. ...
0
1964
by: Przemys³aw Bana¶ | last post by:
Hello! Can anyone help me with calling Novell function from dll? I think my main problem is in translating C variable types to C# types. Here is the code: using System; using System.Collections.Generic; using System.ComponentModel;
11
407
by: John Friedland | last post by:
My problem: I need to call (from C code) an arbitrary C library function, but I don't know until runtime what the function name is, how many parameters are required, and what the parameters are. I can use dlopen/whatever to convert the function name into a pointer to that function, but actually calling it, with the right number of parameters, isn't easy. As far as I can see, there are only two solutions: 1) This one is portable. If...
1
1408
by: Abhishek | last post by:
Hello All I am able to call the function exported by Exe from a dll file im getting the address of the function but whille calling that function im getting access violation Error as bellow my code Function Exported by EXE void __declspec(dllexport) ExeFn() {
0
1530
varuns
by: varuns | last post by:
if i need to call a python function from c, i can use PyImport _Import() python-c API. Following code shows calling python function "add1" from python module "def1" int add(int x, int y) { double answer; Py_Initialize(); PyObject *modname, *mod, *mdict, *func, *arg1, *arg2, *args, *rslt; printf("############INSIDE ADD############\n"); modname = PyString_FromString("def1"); if (modname == NULL)
8
2953
by: Yansky | last post by:
If I have the following function: function foo(){ alert('hi'); } and I don't need to pass any parameters to it, is calling it this way:
1
1598
by: dmitrey | last post by:
hi all, howto check is function capable of obtaining **kwargs? i.e. I have some funcs like def myfunc(a,b,c,...):... some like def myfunc(a,b,c,...,*args):... some like
0
9967
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11202
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7998
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7151
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6029
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4650
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.