How to pass the id list string into in () during storage ()

When writing SQL statements, we often encounter the need to pass the id list string (for example, '2017,... ') as a parameter to the stored procedure,

In the stored procedure, the in clause is used as the condition to filter records. in (@ idList) is used, where @ idList is like '100 ,....'. In this way, an error occurs when the stored procedure is executed. Obviously, this transfer method is not feasible (unless @ idList has only one id such as '001 '),

I tried a lot of methods with my mentor this evening, but I still did not solve it. In constant discussions, there was a sudden inspiration:A temporary table is used,

Store these IDs in a temporary table one by one (this requires some tips and code is provided later), and then call in: in (select id from # tbIdList)

Where # tbIdList is the temporary table with the id saved.

Create table # tbUserId (userID int)
DECLARE @ userID varchar (20)
WHILE (charindex (',', @ Key)> 0)
BEGIN
SET @ userID = substring (@ Key, 0, charindex (',', @ Key ))
SET @ Key = substring (@ Key, charindex (',', @ Key) + 1, len (@ Key ))
Insert into # tbUserId (userID) VALUES (@ userID)
END
Insert into # tbUserId (userID) VALUES (@ Key)

Example: @ Key = '192, 0003'

Call: SELECT * FROM tbUser WHERE userID IN (SELECT userID FROM # tbUserId)

Later, a colleague asked me how efficient I was? I checked the in efficiency and found that we recommend that you use exists instead of in, and the above statement system will automatically convert

Exists (Select * from # tbUserId where tbUser. userID = userID), so the efficiency is no problem.

However, if the query statement after the IN clause involves multiple conditions and is related to the external table, the use of Exists is more efficient.

Http://www.cnblogs.com/zhangchenliang/archive/2010/01/10/1643584.html