如何在单个参数中传递多个值以及如何将 varchar 值转换为整数 [英] how to pass multiple values in single parameter and how to convert varchar value into integers

问题描述

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 时转换失败.

Msg 245, Level 16, State 1, Procedure emp_sp, Line 8
Conversion failed when converting the varchar value ',' to data type int.

任何人都可以指导我.

推荐答案

要使您当前的方法发挥作用，您需要使用动态 Sql，它非常脆弱并且容易受到 Sql 注入攻击.此处的示例

To get your current approach working, you will need to use Dynamic Sql, which will be incredibly fragile and prone to Sql Injection attacks. Example of this Here

更好的方法是通过表值参数:

The better way to do this is through Table Valued Parameters:

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

这篇关于如何在单个参数中传递多个值以及如何将 varchar 值转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！