473,583 Members | 3,200 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need dynamic SQL

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+ch ar(13)+char(10) +officepost+cha r
(13)+char(10)+s treet+char(13)+ char(10)+zip+ci ty+char(13)+cha r(10) from
customer where customer.tooffi ce=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
Jul 20 '05 #1
5 3813
stored procedure with
output variables and call this within my function ?


You can use OPENQUERY .
Jul 20 '05 #2
[posted and mailed, please reply in news]

Ralph (Ra**********@w eb.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+ch ar(13)+char(10) +officepost+
char (13)+char(10)+s treet+char(13)+ char(10)+zip+ci ty+char(13)+cha r(10)
from customer where customer.tooffi ce=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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
[posted and mailed, please reply in news]

Ralph (Ra**********@w eb.de) writes:
So this piece of code should be called in the fieldlist, somehow like
'select customer.cust_n o,dbo.adress('B ill',customer.c ust_no) from customer
where deleted=0' and give in return a char (varchar(4000)? ??)like
'Mr.<cr>Hans Mustermann<cr>s omewhere 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland Sommarskog <es****@sommars kog.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. Misunderstandin g, 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' ???
Jul 20 '05 #5
Ralph (Ra**********@w eb.de) writes:
Misunderstandin g, 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****@sommarsk og.se

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

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

Similar topics

1
3862
by: Marc | last post by:
Hello, I've fiddled with this for quite a while and thought I had the problem solved. I had a version that would successfully compile and run. But then I had to change the code to use a different module, and now it will compile but not run again. I know that functionality in this area is not fully supported yet, but there has to be...
1
3323
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently overwhelmed by useless examples across the web on how to make "dynamic crosstab reports" without myself having a basic understanding about how to retrieve...
2
1620
by: Pasacco | last post by:
dear I want to ask help on this problem. Array a is partitioned into a0 and a1 in main(). Then a1 is partitioned into a2 and a3 in th_partition() function. And I think this problem is something about parameter passing. If someone give me comment, it will be thankful. thankyou very much
1
1765
by: Hasani \(remove nospam\) | last post by:
The way the system works is, you create a user control (ascx) that will be a template and must implement the interface IPageTemplate. You then create one or more user controls (ascx) that implement the IPageContent interface. A page (aspx) must then be created that loads (using Page.LoadControl) the page template, and the page content. The...
4
1339
by: serge calderara | last post by:
Dear all, I need to build a web application which collects data from an SQL server database. SQL server database tables fields can be dynamically created or extended depending on my customer request and can be different customers to customers. Ideally what I would like to build is a web application frame which covers any request and...
4
492
by: ashmangat | last post by:
Hey I am new, but I don't have time to intruduce myself yet. I am intro to C++ and this is a programme I have to write. all the direction are here, It will be very nice of someone to figure this out. note: I only in intro C++ which is about to be finished by the next two weeks. The topic which I am on in my book is "POINTERS" Text from...
23
2521
by: vinod.bhavnani | last post by:
Hello all, I need desperate help Here is the problem: My problem today is with multidimensional arrays. Lets say i have an array A this is a 4 dimensional static array.
2
1495
by: Jeff Rush | last post by:
Forrester Research is doing a study on dynamic languages and has asked that Python be represented. As advocacy coordinator I've volunteered to drive this, collecting answers from the community and locating representatives to participate in interviews. The goal of the study is to: - identify the criteria to use for evaluating such...
18
2281
by: Angus | last post by:
Hello We have a lot of C++ code. And we need to now create a library which can be used from C and C++. Given that we have a lot of C++ code using classes how can we 'hide' the fact that it is C++ from C compilers? Can we have a C header file which uses the functionality of the C++ files and compile this into a lib file?
0
8172
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. ...
0
8317
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7928
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8188
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...
0
6574
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5695
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...
0
3813
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2326
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
0
1151
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.