YiJyun's 研究小間

在SQL語法裡如果需要使用到select * from xxx where ABC in(@ABC) 的問題 由於參數使用有防sql injection

所以會過濾掉一些符號 in(@ABC)的時候會查不到資料 查了一下文章 有人寫了個fouction來重新組字串 

建立的語法如下

CREATE FUNCTION dbo.funcListToTableInt(@list as varchar(8000), @delim as varchar(10))
 RETURNS @listTable table(
   Value INT
   )
AS
 BEGIN
     --Declare helper to identify the position of the delim
     DECLARE @DelimPosition INT
    
     --Prime the loop, with an initial check for the delim
     SET @DelimPosition = CHARINDEX(@delim, @list)

     --Loop through, until we no longer find the delimiter
     WHILE @DelimPosition > 0
     BEGIN
         --Add the item to the table
         INSERT INTO @listTable(Value)
             VALUES(CAST(RTRIM(LEFT(@list, @DelimPosition - 1)) AS INT))
    
         --Remove the entry from the List
         SET @list = right(@list, len(@list) - @DelimPosition)

         --Perform position comparison
         SET @DelimPosition = CHARINDEX(@delim, @list)
     END

     --If we still have an entry, add it to the list
     IF len(@list) > 0
         insert into @listTable(Value)
         values(CAST(RTRIM(@list) AS INT))

   RETURN
 END
 GO

 

使用方式如下

SELECT * FROM Reports WHERE ReportId IN ( SELECT Value FROM funcListToTableInt(@Reports,',') )