Help with sql string execution.

[kingstonr]

HI,
In my application I am sending string to sqlserver for the columns to update.but the proceduare will substitute the value for the column.

for ex like below.
I am sending string with column name and the varibale to refer in sqlserver for value(@columnstoupdate) And I want the values to be substitued when I execute that string. can anybody help me out in this.

declare
@columnstoupdate as nvarchar(max),
@neworiginalfacevalue as decimal(18,2),
@newcurrentfacevalue as decimal(18,2)
set @newcurrentfacevalue=2
set @neworiginalfacevalue=3

set @columnstoupdate='originalFace=@neworiginalfacevalue and CurrentFace=@newcurrentfacevalue'

set @strsql='update positions set '+@columnstoupdate

 

[gmmastros]

The problem here is that you are building your string the wrong way. You need to do it like this...

set @columnstoupdate='originalFace=' + Convert(VarChar(20), @neworiginalfacevalue) + ', CurrentFace=' + Convert(VarChar(20), @newcurrentfacevalue)

Since @neworiginalfacevalue is a numeric, you need to convert it to a string before setting it to @columnstoupdate.

Also, when updating multiple columns, you seperate them with a comma (not 'and').

Alst thing... don't forget your where clause. Otherwise every row will get updated.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LNBruno]

In your example, you're going to have to split up your dynamic SQL...

declare
@column1toupdate as nvarchar(max),
@column2toupdate as nvarchar(max),
@neworiginalfacevalue as decimal(18,2),
@newcurrentfacevalue as decimal(18,2)

set  @newcurrentfacevalue=2
set  @neworiginalfacevalue=3
set @column1toupdate='originalFace'
set @column2toupdate='CurrentFace'

set @strsql='update positions set '+@column1toupdate+'='+@neworiginalfacevalue+', '+@column2toupdate+'='@newcurrentfacevalue

something like that - not tested - headed out to lunch

< M!ke >
Acupuncture Development: a jab well done.

 

[LNBruno]

yeah, what George said ;-)

< M!ke >
Acupuncture Development: a jab well done.