SQL assigning variable fields variable values

[WMitchellCPQ]

Hi there folkes

Ive run into a problem with my update statement. In it I wish to have a variable represting a FieldName and another representing the new value.

Here is my code however the update statement wont work as the variable field name takes the value of the value.

How do I fix this ... any help appreciated

UPDATE dbo.OrderItems
SET @tmpFieldName = @tmpFieldValue
WHERE OrderID = @OrderID

W

 

[JayKusch]

DECLARE @SQLCommand VarChar(200)

SET @SQLCommand =

'UPDATE dbrderItems
SET ' + @tmpFieldName + ' = @tmpFieldValue
WHERE OrderID = @OrderID'

EXEC (@SQLCommand)

Thanks

J. Kusch

 

[checkai]

I think you'll need to create a dynamic sql String to hold your statement and values...then execute it like

set @str = 'UPDATE dbrderItems
SET ' + @tmpFieldName +' = ' + @tmpFieldValue + '
WHERE OrderID = ' + @OrderID

 

[WMitchellCPQ]

Thanks alot guys for your help

 

[WMitchellCPQ]

How do I escape the ' in T-SQL ?

 

[checkai]

What do you mean escape using "'"'s??

dlc

 

[WMitchellCPQ]

I need to escape an apostrophe
e.g.

SET ' + @tmpFieldName +' =  "'+ @tmpFieldValue + '" WHERE OrderID = "' + @OrderID + '=9563'

 

[checkai]

so what is the error you're getting that is making this not work?

 

[WMitchellCPQ]

That code seems to work ok but as such as I change the value for the OrderId to a variable it gives me the following...

set @str = 'UPDATE dbo.OrderItems
SET'+ @tmpFieldName + '="'+ @tmpFieldValue +'"WHERE OrderID ="'+@OrderID +'"'

Server was unable to process request. ---> System.ArgumentException: Syntax error converting the varchar value 'UPDATE dbrderItems
SET Model260RailOnly = "Jim " WHERE OrderID = "' to a column of data type int.



Thanks for looking at this
W

 

[checkai]

I don't think you need the doubl quotes around the @orderID
can you do a print of your statement results?

dlc

 

[SSesham]

Here u go...

set quoted_identifier off

drop table test
create table test(a varchar(15))
insert into test values (1)
insert into test values (2)
insert into test values ('3abc')

declare @sql varchar(50), @tmpFieldName varchar(10), @tmpFieldValue varchar(10),
@OrderID varchar(10)

set @OrderID = '3abc'
set @tmpFieldName = 'a'
set @tmpFieldValue = 'bcd'

select @sql = 'UPDATE Test set ' + @tmpFieldName + ' = ''' + @tmpFieldValue
+ ''' WHERE a = ''' + @OrderID + ''''

print @sql
--OUTPUT will be >> UPDATE Test set a = 'bcd' WHERE a = '3abc'