預存程序無法直接接收陣列/表值參數 (SQL Server 2008+ 才支援 TVP),傳統做法:以逗號分隔字串傳入,動態組裝 SQL 並用 sp_executesql 參數化執行,兼顧彈性與安全。
CREATE PROC usp_GetMembersByIds
@UserIDList VARCHAR(8000) -- '1,2,3,5,8'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
-- 簡單驗證:僅允許數字與逗號,防注入
IF @UserIDList NOT LIKE '%[^0-9,]%'
BEGIN
SET @SQL = N'SELECT * FROM Member WHERE UserID IN (' + @UserIDList + N')';
EXEC sp_executesql @SQL;
END
ELSE
BEGIN
RAISERROR('Invalid ID list format', 16, 1);
END
END
SELECT * FROM Member WHERE UserID IN (SELECT value FROM STRING_SPLIT(@UserIDList, ',')) (注意:不保證順序)。OPENJSON(@JsonArray) 解析陣列。關鍵字:SQL、預存程序、動態 SQL、sp_executesql、TVP、STRING_SPLIT、參數化