January 7, 2003 at 7:19 am
Hi All
I've been searching for the answer to this one in vain, so I thought I'd ask for help.
I have a stored proc. to do an update to a specified row, with the name of the table to be updated and the key value used to identify the target row being passed as parameters to the proc.
I've tried two aproaches to coding the proc., but keep getting syntax errors when I compile it - any ideas ?
Code is below :
CREATE PROCEDURE DBO.RECORD_LINK_ROW_UPDATE @TableName varchar(30), @TransRef integer AS
/* Stamp the <Last_Update> column identified by the <TranRef> param. on the table named
in the <TableName> parameter with the server date & time, to record the latest update
to the row */
begin
/* First aproach */
update (@TableName) set LastUpdate = GetDate()
where TransRefNumber = @TransRef
/* Next I tried This : */
Exec( 'update ' + @TableName + ' set LastUpdate = GetDate() where TransRefNumber = ' + convert( varchar, @TransRef ) )
/* NB : The board seems to strip out my "+" chars from the above line ! */
end
Many Thanks
Andrew Eastwell
January 7, 2003 at 7:48 am
The first approach won't work, the second will. Try declaring a variable first then assigning the TSQL statement to it then executing e.g.
declare @U varchar(200)
select @U = 'update ' + @TableName + ' set LastUpdate = GetDate() where TransRefNumber = ' + convert( varchar, @TransRef )
exec (@u)
Regards,
Andy Jones
.
January 7, 2003 at 8:11 am
Andy
That works a treat - thanks !
Can't figure out why my initial aproach wouldn't run though...
Many Thanks
Andy E
January 7, 2003 at 8:47 am
Because of this
update (@TableName)
you cannot substitute a table name in TSQL this way and thus it fails.
If the second failed then would have to have the error message to have an idea.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply