This looks like a poor database design, but if you have to do it, you need to properly validate the table name, and use
sp_executesql[
^] to execute the query:
CREATE PROC dbo.usp_UpdateItemCount
(
@TableName sysname,
@ItmCode1 nvarchar(50),
@ItmCount1 int
)
As
BEGIN
DECLARE @RealTableName sysname;
DECLARE @Statement nvarchar(max);
SET NOCOUNT ON;
SELECT
@RealTableName = name
FROM
sys.tables
WHERE
name = @TableName
;
If @@ROWCOUNT = 0 RAISERROR('Table "%s" was not found.', 16, 1, @TableName);
SET @Statement = N'UPDATE ' + QUOTENAME(@RealTableName)
+ N' SET [itmcount] = [itmcount] - @itmcount1'
+ N' WHERE [itmcode] = @itmcode1 AND [itmcount] IS NOT NULL';
EXEC sp_executesql @Statement,
N'@ItmCode1 nvarchar(50), @ItmCount1 int',
@ItmCode1 = @ItmCode1,
@ItmCount1 = @ItmCount1
;
END