使用動態 SQL 定位行的問題

一般來說，我是 SQL 新手，所以我在設計的一些儲存過程中遇到了一些問題。我的程序接受 3 個參數。位置（或基本上是表名，varchar）、房間號（varchar）和電話號碼（char），格式為“xxx-xxxx”。這是該過程的程式碼。

ALTER PROCEDURE [dbo].[AddPhoneNumberToRoom]
   @Location    varchar(25),
   @Room        varchar(10),
   @PhoneNumber char(8)

AS
BEGIN
SET NOCOUNT ON;

   DECLARE @String varchar(100);
   SELECT @String = ' UPDATE ' + @Location + 
                    ' SET PhoneNumber = ' + @PhoneNumber +
                    ' WHERE Room = ' + @Room;
   EXEC(@String);
END

我收到的錯誤是：

UPDATE 語句與 FOREIGN KEY 約束“FK_Apartments_PhoneNumbers”衝突。衝突發生在數據庫“CMPhone”、表“dbo.PhoneNumbers”、列“PhoneNumber”中。

電話號碼在 PhoneNumber 表中，所以當我這樣做時：

UPDATE Apartments SET PhoneNumber='123-4567' WHERE Room='2'

它工作得很好。該PhoneNumbers表也看起來像這樣，只有一個數字。

PhoneNumber
-----------
123-4567

PRINT @String;將產生它所說PhoneNumber = 123-4567的（將其視為表達式，123減法4567，等於-4444），PhoneNumber = '123-4567'不像您硬編碼的範例中那樣。所以你應該用兩個單引號轉義你附加的值：

SELECT @String = ' UPDATE ' + @Location + 
                ' SET PhoneNumber = ''' + @PhoneNumber + ''' 
                  WHERE Room = ''' + @Room + ''';';

但更好的是，為了更好地保護自己免受 SQL 注入（請參見此處、此處和此處），您應該 (a) 檢查該@Location表是否有效，(b)QUOTENAME()無論如何，並且 (c) 傳入其他兩個值作為正確鍵入的參數，而不是將它們附加到字元串：

IF OBJECT_ID(N'dbo.' + @Location) IS NOT NULL
BEGIN
 DECLARE @sql NVARCHAR(MAX);
 SET @sql = N'UPDATE dbo.' + QUOTENAME(@Location)
          + N' SET PhoneNumber = @PhoneNumber
               WHERE Room = @Room;';
 EXEC sys.sp_executesql @sql, 
     N'@PhoneNumber CHAR(8), @Room VARCHAR(10)',
     @PhoneNumber, @Room;
END

更好的是，您不應該為每個Location恕我直言單獨的表格 - 位置應該是一列。那麼你根本不需要動態 SQL。

引用自：https://dba.stackexchange.com/questions/106215