473,687 Members | 3,505 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 3822
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
3867
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 someone out there that has got this to work. I am using Python2.3, with py2exe 0.4.2, and win32all 159....
1
3334
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 and assign recordsources, etc., from fields in a query to fields in the report. I see all these...
2
1629
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
1771
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 page content adds itself to the page template. The page template is then added to the aspx's control...
4
1340
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 display any data. This without rebuilding my application but more by applying a kind of configuration...
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 the book:
23
2534
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
1498
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 languages - identify the relevant choices of dynamic languages - identify how the different dynamic...
18
2289
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
8528
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
9072
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
8947
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8782
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8786
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...
0
7621
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4321
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4547
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
1950
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.