Hi,
I am trying to run a update stored procedure where one of the fields is
dynamic eg.
UPDATE table
SET field_ + @.number = @.a_value
WHERE (key = @.key_value)
@.number is chosen by the user and the field_## can be anything from field_01
to field_99.
Is there a way i can do this? The above method doesn’t work.
Any help will be greatly appreciated.
Many ThanksIn t-SQL, you will have to use each columns explcitly in the SET clause,
with commas separating each column assignments. For syntax, refer to the
topic UPDATE in SQL Server Books Online.
Perhaps with Dynamic SQL you might be able to kludge it out. For details,
refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note, it
is possible that you have a flawed design which force you to use such
meaningless constructs in your code.
Anith|||Hi Vortex
consider rewriting as:
EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
key = ' + @.key_value)
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"vortex" wrote:
> Hi,
> I am trying to run a update stored procedure where one of the fields is
> dynamic eg.
> UPDATE table
> SET field_ + @.number = @.a_value
> WHERE (key = @.key_value)
> @.number is chosen by the user and the field_## can be anything from field_
01
> to field_99.
> Is there a way i can do this? The above method doesn’t work.
> Any help will be greatly appreciated.
> Many Thanks
>|||I will give it a try,
I am creating a stored procedure with your update command, if I use the
method you suggested will SQL have to compile the sp every time a new value
is used or will it just compile the once. (Speed is required, that is why I
am using a sp)
Thanks
Khalid
"Chandra" wrote:
> Hi Vortex
> consider rewriting as:
> EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
> key = ' + @.key_value)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "vortex" wrote:
>|||If you can suggest a better way of doing it, i would be a very happy bunny a
s
i have a lot more stored procedures to write :(
thanks
"Anith Sen" wrote:
> In t-SQL, you will have to use each columns explcitly in the SET clause,
> with commas separating each column assignments. For syntax, refer to the
> topic UPDATE in SQL Server Books Online.
>
> Perhaps with Dynamic SQL you might be able to kludge it out. For details,
> refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note,
it
> is possible that you have a flawed design which force you to use such
> meaningless constructs in your code.
> --
> Anith
>
>|||It is not as ease as it seems. For example, Chandra's solution will fail if
@.number is tinyint/int/bigint because you can not those data types have
greater precedence than varchar so sql server will try to convert 'UPDATE
table SET field_' to tinyint/int/bigint and this will give an error. The sam
e
will happen @.a_value, you have to quote it between apostrophes for char /
varchar / datetime values. The same with @.key_value. You will have to use
dynamic sql and bunch on lines to accomodate the statement to the variables
data type.
I will not write about readability and maintenance of your final code, you
can guess what will be the result.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"vortex" wrote:
> I will give it a try,
> I am creating a stored procedure with your update command, if I use the
> method you suggested will SQL have to compile the sp every time a new valu
e
> is used or will it just compile the once. (Speed is required, that is why
I
> am using a sp)
> Thanks
> Khalid
> "Chandra" wrote:
>
>|||>> If you can suggest a better way of doing it,..
Better way of doing an UPDATE or changing the design? Regarding the UPDATE,
did you refer to the manual for exact syntax?
Regarding the design, with simple one-liners as in your initial post, it is
hard to suggest anything meaningful. Post some more information regarding
this table, the entity type that is being modelled and the attributes
involved. Also provide some details regarding the business model and how
this table fits into the overall schema.
Generally it is hard to provide accurate design suggestions over newsgroup
responses, however with the above requested info, you could perhaps get
started.
Anith
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment