473,799 Members | 3,638 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Dynamic SQL and function

Hi i have a problem i have the following Trans SQL statement in a
function

SET @desc= (Select description From table Where id = @id)

Now i want to make table dynamic. Something like this

SET @desc= (Select description From @table Where id = @id)

Yeah yeah i know i can't do this:

SET @desc= EXEC('Select description From '+ @table + ' Where id = '+
@id)
is not possible

Writing to a temporary table is prohibited by functions. But i need a
function because i want to use the result in a query..

Can someone help me?

Hennie
Jul 20 '05 #1
2 7403

"Hennie de Nooijer" <hd********@hot mail.com> wrote in message
news:19******** *************** **@posting.goog le.com...
Hi i have a problem i have the following Trans SQL statement in a
function

SET @desc= (Select description From table Where id = @id)

Now i want to make table dynamic. Something like this

SET @desc= (Select description From @table Where id = @id)

Yeah yeah i know i can't do this:

SET @desc= EXEC('Select description From '+ @table + ' Where id = '+
@id)
is not possible

Writing to a temporary table is prohibited by functions. But i need a
function because i want to use the result in a query..

Can someone help me?

Hennie


See "SELECT @local_variable " in Books Online:

select @desc = description
from @table
where id = @id

Simon
Jul 20 '05 #2

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:41******** **@news.bluewin .ch...

"Hennie de Nooijer" <hd********@hot mail.com> wrote in message
news:19******** *************** **@posting.goog le.com...
Hi i have a problem i have the following Trans SQL statement in a
function

SET @desc= (Select description From table Where id = @id)

Now i want to make table dynamic. Something like this

SET @desc= (Select description From @table Where id = @id)

Yeah yeah i know i can't do this:

SET @desc= EXEC('Select description From '+ @table + ' Where id = '+
@id)
is not possible

Writing to a temporary table is prohibited by functions. But i need a
function because i want to use the result in a query..

Can someone help me?

Hennie


See "SELECT @local_variable " in Books Online:

select @desc = description
from @table
where id = @id

Simon


Oops, sorry - I read your post far too quickly. Basically, no, you cannot
use dynamic SQL in a function, and you probably shouldn't use it anyway:

http://www.sommarskog.se/dynamic_sql.html#Dyn_table

Simon
Jul 20 '05 #3

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

Similar topics

6
2835
by: Vasileios Zografos | last post by:
Hello, I have a function that generates some values (e.g. vertices in 2d space) the number of which I dont know. So, it could generate 20 vertices, 100 vertices, or even 1 vertex. void generateVals() { ..... //generate some values
4
7701
by: Scott Lyons | last post by:
Hey all, Can someone help me figure out how to pass a dynamic array into a function? Its been giving me some trouble, and my textbook of course doesnt cover the issue. Its probably something simple, but its just not popping into my mind at the moment. My little snippet of code is below. Basically, the studentID array is dynamic so it will fit any length of a Student's Name. What I'm trying to do is place this chunk of code into a...
14
14871
by: Ina Schmitz | last post by:
Hello, I would like to get the logical and physical reads for every sql statement executed. Thatfore, I used the command "db2 get snapshot for dynamic sql on <mydatabase>". There, I could see the logical and physical reads from bufferpool for every statement monitored. Am I right? But now, I would like to read these results out of a table so that I could process them automatically. In which table(s) are the results of snapshot for...
1
17681
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
5
3763
by: swarsa | last post by:
Hi All, I realize this is not a Palm OS development forum, however, even though my question is about a Palm C program I'm writing, I believe the topics are relevant here. This is because I believe the problem centers around my handling of strings, arrays, pointers and dynamic memory allocation. Here is the problem I'm trying to solve: I want to fill a list box with a list of Project Names from a database (in Palm this is more...
2
2946
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic sub-report will capture what grades the student has achieved in a list of different subjects and the reason I need it to be dynamic is that students take different subjects. Basically I've been trying to doctor the KB article on dynamic
7
8224
by: Jo | last post by:
Hi, How can i differentiate between static and dynamic allocated objects? For example: void SomeFunction1() { CObject *objectp = new CObject; CObject object;
9
3631
by: Tarscher | last post by:
hi all, I have this seemingly simple problem. I have lost a lot of time on it though. When a user selects a value from a dropdownlist (static control) a dynamic control is generated. I have to create the dynamic controls in the OnInit stage of the lifecycle. Since data from static controls is not yet available in the OnInit stage I can't know what dynamic control I have to create.
1
7979
by: Peterwkc | last post by:
Hello all expert, i have two program which make me desperate bu after i have noticed the forum, my future is become brightness back. By the way, my problem is like this i the first program was compiled and run without any erros but the second program has a run time error when the function return from allocate and the ptr become NULL. How to fixed this? Second Program: /* Best Method to allocate memory for 2D Array because it's ...
14
3840
by: vivek | last post by:
i have some doubts on dynamic memory allocation and stacks and heaps where is the dynamic memory allocation used? in function calls there are some counters like "i" in the below function. Is this stored in stack. If yes whether it will be deleted on exiting from the function. is dynamic memory allocation needed for this purpose
0
9687
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
9541
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
10485
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
10252
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...
0
9073
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...
1
7565
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
5463
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...
2
3759
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2938
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.