how to pass multiple values in single parameter and how to convert varchar value into integers

Question

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')

This is my stored procedure

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

Here I execute it manually

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

but I get this error:

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

Anyone can guide me.

Well, the `IN()` operator expects a **list of items** (in your case: a list of strings) - but what you're supplying to it is **a single string**. What you need to do is **split up** that comma-separated string into a list of strings and then use that in your `IN` - or better yet: use a **table-valued parameter** to pass in a proper list of multiple values to your stored procedure in the first place! — marc_s, Apr 25 '14 at 05:04

StuartLC · Answer 1 · 2014-04-25T05:49:56.463

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

And called like so:

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 here

how to pass multiple values in single parameter and how to convert varchar value into integers

1 Answers1

Linked