如何通过多个值在单个参数，以及如何为varchar值转换为整数(how to pass multip

CREATE TABLE students
( id INT,
  NAME  varchar(20)
)

INSERT INTO students(id,name)VALUES(1,'Danny')
INSERT INTO students(id,name)VALUES(2,'Dave')
INSERT INTO students(id,name)VALUES(3,'Sue')
INSERT INTO students(id,name)VALUES(4,'Jack')
INSERT INTO students(id,name)VALUES(5,'Rita')
INSERT INTO students(id,name)VALUES(6,'Sarah')

这是我的存储过程

alter PROCEDURE emp_sp 
(
@std_id as VARCHAR(500),
@std_name as varchar(500)
)
AS
begin 
SELECT *FROM Students s
WHERE s.id IN(convert(INT,@std_id) ,',')
AND 
s.NAME IN(@std_name)
END
GO

在这里，我手动执行它

EXEC dbo.emp_sp @std_id='1,2,3', @std_name='"Danny","Dave","Sue"'

但我得到这个错误：

消息245，级别16，状态1，过程emp_sp，8号线
转换转换VARCHAR值“” int数据类型时失败。

任何人都可以指导我。

为了让您当前的工作方式，您将需要使用动态SQL，这将是令人难以置信的脆弱，容易受到SQL注入攻击。这个例子在这里

更好的方式来做到这一点是通过表值参数：

CREATE TYPE ttStudentIDs AS TABLE
(
  ID INT
);
GO

CREATE TYPE ttStudentNames AS TABLE
(
  Name VARCHAR(20)
);
GO

CREATE PROCEDURE dbo.emp_sp 
(
   @stdIds ttStudentIDs READONLY,
   @stdNames ttStudentNames READONLY
)
AS
begin

   SELECT s.ID, s.Name
   FROM Students s
   INNER JOIN @stdIds si 
   ON s.ID = si.ID

   UNION

   SELECT s.ID, s.Name
   FROM Students s
   INNER JOIN @stdNames  sn
   ON s.Name = sn.Name;
END
GO

并呼吁像这样：

DECLARE @Ids AS ttStudentIDs;
DECLARE @Names AS ttStudentNames;

INSERT INTO @Ids VALUES (1),(2),(3);
INSERT INTO @Names VALUES ('Danny'),('Dave'),('Sue');

EXEC dbo.emp_sp @Ids, @Names;

SqlFiddle这里