T-SQL A problem while passing CSV string into a stored procedure

Question

I have that procedure which returns rows associated by ID with passed argument, i.e 1,5,7,9

ALTER PROCEDURE [dbo].[get_data]
 @MyCodes as varchar(max) = ''
AS
BEGIN
 DECLARE @query as nvarchar(max)

 set @query = 'SELECT name FROM user WHERE id IN (@p_MyCodes)'

 exec SP_EXECUTESQL @query,
                        N'@p_MyCodes varchar(max)', 
                        @p_MyCodes = @MyCodes
END

That procedure generates an error : Error converting data type varchar to numeric. when I pass as an argument e.g. 3,7,5

What's wrong ?

Check out: adathedev.co.uk/2010/02/…

AdaTheDev
– AdaTheDev

2010-07-21 12:41:11 +00:00
Commented Jul 21, 2010 at 12:41 — AdaTheDev
– AdaTheDev, Commented Jul 21, 2010 at 12:41

Community · Accepted Answer · 2017-05-23 11:48:28Z

3

I don't think this is going to accomplish what you are expecting it to. The error you are getting is because it can't convert the string '3,7,5' to a number (note that it is NOT trying to parse out your individual values).

Two ways to get what you want:

1) Create a table value function that takes a CSV string and returns the results (I'm sure there are many on the web; Here's a related question: Split function equivalent in T-SQL?). This is nice because you can get rid of the SP_EXECUTESQL sproc call entirely; Your query becomes:

SELECT name FROM user where id IN (SELECT value FROM dbo.f_Split(@p_MyCodes))

2) Change your set to something like:

set @query = 'SELECT name FROM user WHERE id in (' + @p_MyCodes + ')'

I don't recommend #2, it offers a SQL injection hole.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jul 21, 2010 at 12:31

Chris Shaffer

32.6k5 gold badges52 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tony Over a year ago

I used the 2nd option, but then I needed to change the 'exec' clausule into: exec SP_EXECUTESQL @query, N'@p_MyCodes nvarchar(max)','' and then everything worked perfetly :)

Florian Reischl · Accepted Answer · 2011-12-12 19:44:49Z

1

You cannot pass the ID list as parameter. You could create the SQL statement by concatenating:

set @query = 'SELECT name FROM user WHERE id IN (' + @MyCodes + ')'
exec SP_EXECUTESQL @query

Though, this disables any kind of execution plan re-usage and enables SQL injection

A better solution would be to split the list into a temp table (or table variable) and using a JOIN. Last year, I wrote a blog post about different ways to split strings in T-SQL: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html

edited Dec 12, 2011 at 19:44

user142162

answered Jul 21, 2010 at 12:34

Florian Reischl

3,8761 gold badge27 silver badges19 bronze badges

Comments

Guffa · Accepted Answer · 2010-07-21 12:35:43Z

0

You can't use a comma separated string with the in operator, you have to use the actual values. So, you either have to split the string up and put the values in a temporary table, or concatenate the string into the query:

set @query = 'SELECT name FROM user WHERE id IN (' + @p_MyCodes + ')'

Note that this opens up a potential security hole for SQL injection. You should not do this if you don't have full control over where the string comes from.

answered Jul 21, 2010 at 12:35

Guffa

703k112 gold badges760 silver badges1k bronze badges

Collectives™ on Stack Overflow

T-SQL A problem while passing CSV string into a stored procedure

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related