使用"IN"存储过程参数SQL的语句 [英] Using "IN" Statement for Stored Procedure Parameter SQL

问题描述

我有一个存储过程，带有一个名为"@IDs"的输入参数.这是从我的应用程序中填充的，该应用程序将以以下格式填充它:"2、30、105".当然，此参数中的值的数量会有所不同(例如:有时@ID有时为'100，2005，2，510')我的存储过程非常简单.我有一个名为人"的表.我正在尝试编写此查询:

I have a stored procedure with one input parameter called "@IDs". This gets populated from my application which will populate it in the following format: '2, 30, 105'. The number of values inside this parameter will differ of course (For example: sometimes @IDs will be '100, 2005, 2, 510') My stored procedure is very simple. I have a table called "Persons". I'm trying to write this query:

Select * From Persons Where P_Id in (@IDs)  

P_ID是表中的主键.我得到的错误是将varchar值"2、3、4"转换为数据类型int时转换失败."任何建议将不胜感激.谢谢.

P_ID is the primary key in my table. The error I get is 'Conversion failed when converting the varchar value '2, 3, 4' to data type int.' Any suggestions will be greatly appreciated. Thanks.

推荐答案

您可能需要做一个准备好的语句.这个想法是建立选择语句，然后执行它.这是有关如何执行此操作的示例...

you might need to do a prepared statement. The idea is to build the select sentence and then execute it. Here's an example on how to do it...

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
                 IN sqlq VARCHAR(5000)
                  ) COMMENT 'Executes the statement'
BEGIN
  SET @sqlv=concat(concat('select abc from yourtable where abc in (',sqll),')');
  PREPARE stmt FROM @sqlv;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ; 

只需更改要执行的查询即可.

Just change the query for the one you want to execute.

这篇关于使用"IN"存储过程参数SQL的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！