Solve the Problem of select * From tablename where ID in (@ IDs)

There is a problem:

In SQL Server 2005, there is a student table with the following structure:

Students (

Id int,

Name varchar (100)

)

Problem: the student ID is 1, 2, 3, 4, 5.

My traditional method is to construct SQL statements in the class library and call ADO. Net to perform the query operation. This method can be used to query the desired structure. However, this problem has also been encountered in the previous storage process. The SQL statement is as follows,

Declare @ IDs varchar (100)

Set @ IDs = '1, 2, 3, 4, 5 ';

Select * from students where ID in (@ IDs)

Error message: failed to convert ''1, 2, 3, 4, 5 'to data type int

Later, I searched the internet and skillfully used the charindex Method to Solve the Problem of passing strings.

Here is a solution.

Declare @ IDs varchar (100)

Set @ IDs = '1, 2, 3, 4, 5 ';

Select * from students where charindex (',' + Cast (ID as varchar) + ',' + @ IDs + ',')> 0

The execution efficiency of this method has not been studied. However, this problem is cleverly solved.

Use charindex see blog: http://www.cnblogs.com/yoolonet/archive/2010/11/25/1887868.html