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

Source: Internet
Author: User

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)
SET @ userID = substring (@ Key, 0, charindex (',', @ Key ))
SET @ Key = substring (@ Key, charindex (',', @ Key) + 1, len (@ Key ))
Insert into # tbUserId (userID) VALUES (@ userID)
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.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.