visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MS SQL Server :: SQL SERVER 2000 - UDF: Update statement issue
By: nguyenn U.S.A.  Date: 29/08/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
Hello experts,

I create a function,this function will return a table (@MyTable)
Suppose there are 6 fields in the table:
ID, FIELD_1, FIELD_2, FIELD_3, FIELD_4, FIELD_5


In my loop, i want to update my table, the problem is rathan using FIELD_1,2,3,etc..., i want to use a variable to indicate what field i want to update. Unfortunately, in the below source code:

set @Keyword= 'my value'
Set @KeyField = 'FIELD_' + cast(@RCount as varchar (3)) -- line 2
update @MyTable SET @KeyField = cast(@Keyword as varchar(5)) where [IP] = @ID -- line3

the @KeyField value will set to @Keyword value when I run the update statement
i.e.
after running line 2:
@KeyField = FIELD_2
after running line 3:
@KeyField = 'my value'

therefore the update statement never update mytable as i wanted.

how could we overcome this problem?

thanks
By: VGR Date: 29/08/2003 07:15:00 English  Type : Assist
heh. That's what you programmed 8-)))

you do a set @keyfield=cast(@keyword)... that's normal !

you definitely should use a fast front-end language (like PHP) and not a snail UDF full of user variables that don't do what you want. You lack a "dereferencing" operator to be able to say UPDATE... SET [value of @Keyfield] =
By: nigelrivett Date: 30/08/2003 03:32:00 English  Type : Answer
In a function you cannot use dynamic sql so you are limited to a series of If statements
By: nguyenn Date: 02/09/2003 23:18:00 English  Type : Comment
Hi VGR , not sure what do you mean "lack a "dereferencing" operator, but i tried:

update @ItemKeywords SET [@KeyField] = @Keyword where [EDP_NO] = @EDPNum

but i got an error:
Invalid column name '@KeyField'

I even tried:
update @ItemKeywords SET ([@KeyField] = @Keyword) where [EDP_NO] = @EDPNum

and got error:
Incorrect syntax near '('.

You have any idea?
thanks
By: nguyenn Date: 02/09/2003 23:30:00 English  Type : Comment
Okie, I guess I cant use the update statement include [value of @KeyField] = ..., unfortunately I dont know how to use the fast front-end language as you suggested.

I may use different method,
using
insert into @ItemKeywords values (@SetOfKeywords)

then got another error:
Insert Error: Column name or number of supplied values does not match table definition.
By: nguyenn Date: 03/09/2003 00:14:00 English  Type : Comment
thank you guys, both of you have suggested me the solution, I would split my points to both of you,
I very appreciated for your big help!!!!

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 344.625000 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page