October 18, 2002 at 8:08 pm
I want to use update table query in SP:
I am passing parameter to SP. The value of parameter itself is field name for one of the table. After executing action inside, at the end of SP, I would like to update the table field(Here in this case,My variable is feild name)... How can I use update table query.
I did like this which does not work:
UPDATE tablename SET @inputvar = @somevalue
WHERE condition
::here @inputvar is field name
@somevalue is value assigned to field.
Please reply soon. May contact me at kulu147@hotmail.com
October 19, 2002 at 11:01 am
You have to use dynamic sql, like this:
exec(sql) or sp_executesql (@sql)
Andy
October 21, 2002 at 4:51 am
Further to that you shouldpass your variables in using sp_executesql
i.e
declare @sql nvarchar(100)
set @sql = 'UPDATE tablename SET ' + @inputvar + '= @somevalue
WHERE condition'
exec sp_executesql @sql, N'@somevalue int', @somevalue
This ensures the sql is properly parameterised by the sql server
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply