|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
increment value in variable column nameI'm trying to increment the value of a variable column name, however the Update statement is parsing in the literal column name rather than its value. It works fine when I just set its value: SET @SQL = 'UPDATE CTE_Revs SET ' + @col_name + ' = ' + cast(@amt as varchar) + 'WHERE cust_key = ' + @cust_key But when I try to increment is where it blows up: SET @SQL = 'UPDATE CTE_Revs SET ' + @col_name + ' = ' + @col_name + cast(@amt as varchar) + 'WHERE cust_key = ' + @cust_key Can anyone point me in the right direction here? Thank you for your help, Marc Miller >> Can anyone point me in the right direction here? << Do not write code like this. You are supposed to know what you areupdating and not make it up on the fly. Remeber any of this stuff from your first software engineering course?? This is not just SQL; this is basic programming skills. Coupling, cohesion, strong typing, etc.?? Thank you for your help.
Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1126280195.328591.81400@f14g2000cwb.googlegroups.com... >>> Can anyone point me in the right direction here? << > > Do not write code like this. You are supposed to know what you are > updating and not make it up on the fly. Remeber any of this stuff from > your first software engineering course?? This is not just SQL; this is > basic programming skills. Coupling, cohesion, strong typing, etc.?? > This is not the way to write UPDATEs. Typically you should create one
or more update procedures per table and pass the set of values as parameters. So your UPDATE might look like this: UPDATE cte_revs SET col1 = COALESCE(@col1,col1), col2 = COALESCE(@col1,col2), col3 = COALESCE(@col1,col3), ... WHERE key_col = @key_col Parameterizing column names is a really bad idea in general. Why wouldn't you know the names of your columns? -- David Portas SQL Server MVP -- I think you need + '+' + a couple of times. Make sure you validate the
parameters before you use them. Read up on SQL Injection. Show quote "Marc Miller" <mm1***@hotmail.com> wrote in message news:edTkeOVtFHA.3316@TK2MSFTNGP12.phx.gbl... > Hi all, > > I'm trying to increment the value of a variable column name, however > the Update statement is parsing in the literal column name rather than > its value. It works fine when I just set its value: > > SET @SQL = 'UPDATE CTE_Revs > SET ' + @col_name + ' = ' + cast(@amt as varchar) + > 'WHERE cust_key = ' + @cust_key > > But when I try to increment is where it blows up: > > SET @SQL = 'UPDATE CTE_Revs > SET ' + @col_name + ' = ' + @col_name + cast(@amt as varchar) + > 'WHERE cust_key = ' + @cust_key > > Can anyone point me in the right direction here? > > Thank you for your help, > > Marc Miller > > > Brian,
That did it! I thank you very much. Marc Miller Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23r3iXXVtFHA.3536@TK2MSFTNGP10.phx.gbl... >I think you need + '+' + a couple of times. Make sure you validate the > parameters before you use them. Read up on SQL Injection. > > "Marc Miller" <mm1***@hotmail.com> wrote in message > news:edTkeOVtFHA.3316@TK2MSFTNGP12.phx.gbl... >> Hi all, >> >> I'm trying to increment the value of a variable column name, however >> the Update statement is parsing in the literal column name rather than >> its value. It works fine when I just set its value: >> >> SET @SQL = 'UPDATE CTE_Revs >> SET ' + @col_name + ' = ' + cast(@amt as varchar) + >> 'WHERE cust_key = ' + @cust_key >> >> But when I try to increment is where it blows up: >> >> SET @SQL = 'UPDATE CTE_Revs >> SET ' + @col_name + ' = ' + @col_name + cast(@amt as varchar) + >> 'WHERE cust_key = ' + @cust_key >> >> Can anyone point me in the right direction here? >> >> Thank you for your help, >> >> Marc Miller >> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics