0

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 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.

Keven Diuliy
  • 131
  • 2
  • 5
  • 12

3 Answers3

1

One way is use dynamic SQL. That is generate the SQL as a string and then execute it.

An easier way (perhaps) is to use like:

where concat(', ', @IDS, ', ') like concat('%, ', id, ', %')

Note that this puts the separator at the beginning and end of the expressions, so "10" won't match "11010".

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

1) Show your code.

2) You've probably tried to pass in all the values as one parameter. That doesn't work. You have to list them as separate parameters and then bind them as separate parameters.

Yes, this makes it hard to use stored procedures when the number of in parameters may change.

Danack
  • 24,939
  • 16
  • 90
  • 122
0

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.

lemil77
  • 341
  • 1
  • 8
  • Is it true CONCAT only works in 2012? – Keven Diuliy Mar 07 '13 at 00:57
  • Im using it in mysql 5 without a problem. Also if you do not want to use the function just do it the implicit way. For quoted strings, concatenation can be performed by placing the strings next to each other: mysql> SELECT 'My' 'S' 'QL'; -> 'MySQL' – lemil77 Mar 07 '13 at 01:01
  • I really appreciate your help, but I'm using SQL 2008 R2 and concat is not a built in function. – Keven Diuliy Mar 07 '13 at 01:04