Need dynamic SQL

[Ralph]

Hi all,

I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need to
implement a logic to receive an adress build out of various user definable
fields from various user defined tables. The function is already implemented
in the Client software and as UDF-compliant in MySQL and Oracle. Now there's
just MS-SQL left...

The problem now is for sure, I'm in need of a scalar return value (a
varchar) composed out of a dynamic sql-context. The wohle informations are
placed in a definition table and would result in a sql-string like "select
@cFieldlist from @cTable where @cWhere", e.g. "Select
title+firstname+char(13)+char(10)+lastname+char(13 )+char(10)+officepost+char
(13)+char(10)+street+char(13)+char(10)+zip+city+ch ar(13)+char(10) from
customer where customer.tooffice=1"...

The problem is of course the calling of the dynamic sql statement within my
function. As read in different articles, this is impossible in MS-SQL. Is
there any possibility to workaround like creating a stored procedure with
output variables and call this within my function ?

Any hint is welcome
Ralph

[Yura]

stored procedure with

output variables and call this within my function ?

You can use OPENQUERY .

[Erland Sommarskog]

[posted and mailed, please reply in news]

Ralph (Ra**********@web.de) writes:

I'm a newbie to MS-SQL UDFs and seem to have a real big problem. I need
to implement a logic to receive an adress build out of various user
definable fields from various user defined tables. The function is
already implemented in the Client software and as UDF-compliant in MySQL
and Oracle. Now there's just MS-SQL left...

The problem now is for sure, I'm in need of a scalar return value (a
varchar) composed out of a dynamic sql-context. The wohle informations are
placed in a definition table and would result in a sql-string like "select
@cFieldlist from @cTable where @cWhere", e.g. "Select
title+firstname+char(13)+char(10)+lastname+char(13 )+char(10)+officepost+
char (13)+char(10)+street+char(13)+char(10)+zip+city+ch ar(13)+char(10)
from customer where customer.tooffice=1"...

The problem is of course the calling of the dynamic sql statement within
my function. As read in different articles, this is impossible in
MS-SQL. Is there any possibility to workaround like creating a stored
procedure with output variables and call this within my function ?

You cannot call stored procedures from UDF:s. The idea is that a UDF
should not change the state of the database. Therefore you are severely
constraint in what you can do.

You can call extended stored procedures, though, so you could call
xp_cmdshell for do what you need from a second connection or a custom-
written XP for the task. Yura suggested OPENQUERY, and OPENQUERY is indeed
another loophole. But since OPENQUERY requires a constant SQL string,
you are still in a dead end.

And I would not really encourage the XP solution either. It may be
logicially correct, but performance will be poor. And if you use
xp_cmdshell there are security considerations. (And you have to be
really desperate to write your own XP.)

As I didn't really understand the problem, it's difficult to come
with suggestions, but you should probably use a stored procedure instead.
I have an article on my web site, which discusses the techniques
around this: http://www.sommarskog.se/share_data.html

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Erland Sommarskog]

[posted and mailed, please reply in news]

Ralph (Ra**********@web.de) writes:

So this piece of code should be called in the fieldlist, somehow like
'select customer.cust_no,dbo.adress('Bill',customer.cust_n o) from customer
where deleted=0' and give in return a char (varchar(4000)???)like
'Mr.<cr>Hans Mustermann<cr>somewhere street 10 <cr><cr>55555 ZipLand'. To
find the fields and the correct records, this code has to scan the
definition table, read the fields to use and under which conditions to use
and perhaps the tables which should be joined to the query, all written
there.

Is this really impossible ???

Generally, if you want to do something in an RDBMS, and also insist on a
certain syntax, it is not possible, unless you happen to pick the syntax
of the RBDMS in question.

For SQL Server, write a stored procedure that returns the address in
output parameters. If you need to handle many at a time, you can use a
temp table. See further here: http://www.sommarskog.se/share_data.html.

Sharing code between RBDMSs is very difficult, because there is a great
variety in what they support. It may simplify programming and maintenance,
to get the data to the client, using basic SQL which works in any RDBMS.
This may be not be good for performance, as more data will be shoveled
across the network.

Then again, your complex and dynamic data model is apparently not designed
for performance anyway. RBDMSs work from the idea that tables and columns
are stable, and that your schema does not change dynamically. So one idea
could be to have custom fields as rows rather than columns. This can lean
to unwieldy self-join, but at least you will not need dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Ralph]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...

[posted and mailed, please reply in news]

Generally, if you want to do something in an RDBMS, and also insist on a
certain syntax, it is not possible, unless you happen to pick the syntax
of the RBDMS in question. Misunderstanding, we don't insist on syntax, we insist on
functionality. The implementation is of course different on every
RDBMS. But til now every RDBMS gives us a possible way to implement
this stuff with more or less knowledge and doing. Hard to believe
MS-SQL can't...
For SQL Server, write a stored procedure that returns the address in
output parameters. If you need to handle many at a time, you can use a
temp table. See further here: http://www.sommarskog.se/share_data.html. A stored procedure isn't useful, I need a scalar return value... This
function is called within a reportengine and there is no other
processhandling than straight forward doing a select.
Sharing code between RBDMSs is very difficult, because there is a great
variety in what they support. It may simplify programming and maintenance,
to get the data to the client, using basic SQL which works in any RDBMS.
This may be not be good for performance, as more data will be shoveled
across the network. There is a lot of functionality on the client side, even the discussed
functionality is implemented at the client. But this really is a
dead-end for performance tuning, so a server-based solution is
necessary.
Then again, your complex and dynamic data model is apparently not designed
for performance anyway. RBDMSs work from the idea that tables and columns
are stable, and that your schema does not change dynamically. So one idea
could be to have custom fields as rows rather than columns. This can lean
to unwieldy self-join, but at least you will not need dynamic SQL.

It's simply not true, that a flexible and dynamic system can't have a
good performance. It's just more and harder work to implement. But the
maintainance and most of all the customers (and this software is very
very succesfull) are the best reason to do this work.

This just so far, back to the technical problem. Just one question
remaining, are these so called extended procedure only procedure or
are within the c-dll also functions like we need possible ? To be more
precise, is it possible to call a XP-Funtion like 'select
MyXpFunc('Test') from customer' ???

[Erland Sommarskog]

Ralph (Ra**********@web.de) writes:

Misunderstanding, we don't insist on syntax, we insist on
functionality. The implementation is of course different on every
RDBMS. But til now every RDBMS gives us a possible way to implement
this stuff with more or less knowledge and doing. Hard to believe
MS-SQL can't...
Well, MS-SQL can, but...
A stored procedure isn't useful, I need a scalar return value... This
function is called within a reportengine and there is no other
processhandling than straight forward doing a select.
....you are insisting on a specific syntax.

You would have to invoke a batch first calls the stored procedures,
which puts the data in a variable or a temp table (have you looked at
http://www.sommarskog.se/share_data.html yet?), and then SELECTs the
data to the client. Presuming that you are looking for many rows
at a time, this is the only way to a scalable solution.

So that batch will look different than from what it does for MySQL,
but the syntax you wished to use included dbo.yourfunc(), and I don't
think you have the dbo bit on MySQL, so you are already prepared to
handle different syntax.
This just so far, back to the technical problem. Just one question
remaining, are these so called extended procedure only procedure or
are within the c-dll also functions like we need possible ? To be more
precise, is it possible to call a XP-Funtion like 'select
MyXpFunc('Test') from customer' ???

No, there are no extended functions, but you can call extended stored
procedures from a user-defined function. So you could write a extended
stored procedure, which returns the value in an OUTPUT parameter, and
then the scalar UDF could return that function.

But: 1) An error like an access violation in an extended stored procedure
is no small accident - the entire SQL Server perishes.
2) You will get one more - and odd - component do deploy.
3) This is definitely not going to scale.

In short: don't go there.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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