a proc to update a selected single field

[joebickley]

Hi

Im trying to create a proc to update a single field in a table, but that field can be different each time. ie i pass in the field name and the value i want it to be set to. What i have tried to do build and SQL statement into a string and execute it. This works but only from SQL server not from the .ASP page i want it to work from. I think its something to do with QUOTED_IDENTIFIER.

Heres my code, any ideas or alternative methods for doing this?



create Procedure dbo.SP_UpdateSingleField

@colname nvarchar(50),
@colvalue nvarchar(50)
as

set QUOTED_IDENTIFIER OFF

declare @sqltext nvarchar(500)

set @sqltext = 'update tbl_claimdata set ' + @colname + ' = "' + @colvalue + '" where referencenumber = 2'

exec (@sqltext)


Thanks

Joe

 

[nigelrivett]

set @sqltext = 'update tbl_claimdata set ' + @colname + ' = ''' + @colvalue + ''' where referencenumber = 2'

A single quote is the string delimitter not double quote.
Within a string use 2 single quotes.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[LakshmiKiran]

Hi,
Or Probably you can use casting also instead of setting the Quoted_Identifier Off. Like

set @sqltext = 'update tbl_claimdata set ' + cast(@colname as varchar) + ' = ' + cast(@colvalue as varchar) + ' where referencenumber = 2'

 

[joebickley]

thanks nigelrivett didnt realise i could use single quotes in 3's

it sorted it out thanks