You should be able to create an update string using the variable and then use the Execute() command to fire the variable
In this case, I'd also recommend passing in the whole where clause instead of just the pkey.id.
CREATE PROCEDURE usp_updateUserID
@Tablename nvarchar(128),
@pWhere nvarchar(128)
AS
declare @cSQL varchar(1000)
set @cSQL = 'UPDATE ' + @Tablename +
' SET UserID= SYSTEM_USER
FROM ' + @TableName + ', Inserted
WHERE ' + @pWhere
Execute(@cSQL)
>I'm try to write sp that will update UserID on Inserted/Updated Row.
>I wrote a trigger but I've to update code on all table.. So, I think to write this sp to make code more modular. But the problem is I must specify which row will update by use Primary Key Column like this:
>
>CREATE TRIGGER Item_InsUpd ON Item
>FOR INSERT, UPDATE
>AS
> UPDATE Item
> SET UserID = SYSTEM_USER
> FROM Item, Inserted
> WHERE Item.ID = Inserted.ID
>
>Then if I want this code can use with every table by write it down into Sp.
>
>CREATE PROCEDURE usp_updateUserID
> @Tablename nvarchar(128),
> @pKey nvarchar(50)
>AS
> UPDATE @Tablename --Got a syntax error on this line!
> SET UserID= SYSTEM_USER
> FROM @TableName, Inserted
> WHERE @pKey.ID = Inserted.ID --This line I'm not sure about syntax??
>
>Problem on a varaity of primary key column that difference in each table ...That I must use WHERE... as:
>
>WHERE Ap.ID = Inserted.ID
>
>even a composite key like this:
>
>WHERE Item.ID = Inserted.ID AND Price.ID = Inserted.ID
>or
>WHERE Doc.Docno = Inserted.Docno And Doc.Prefix = Inserted.Prefix
>
>Main question is: It's have variety of key to use.
>Second is: I can't use @Tablename after Update... Why??? Error say I'm not declare @Tablename yet???
>How could a smarter way to use variable and parameter on Sp like this?
Insanity: Doing the same thing over and over and expecting different results.