howto: calling sp_executesql from a function ?

[Clausmeyer]

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
*************** *************** *************** *************** *

[Erland Sommarskog]

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

[Clausmeyer]

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
*************** *************** *************** *************** *

[Erland Sommarskog]

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