passing in a varchar

Question

passing in a varchar

zishanz

SSCommitted

Points: 1852
More actions
April 28, 2002 at 5:59 pm

#78794

Hi, I got a problem here:
in my sp, I pass in a string containing
a list of userid separated by ','
like this:
@mv_string = '1,2,3,4,5,6'
create sp (@mv_string varchar(1000) )
as
BEGIN
select *
from my_table
where user_id in (@mv_string)
user_id in my_table is numeric
What I want is 'where user_id in (1,2,3,4,5,6).
But when I exec the sp, it complaints that
can't convert varchar to numeric.
Is any work around, or any standard way to resove this kind of problem?
Thanks in advance.
Abby

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Nothing simple. You can set up a bunch of int parameters based on what you think the max you'll ever need is and build your logic based around them. Or you can split the string parameter and convert each part to an int, then build up a sql string and exec() it. A variation of that would be to split the string and insert the values into a table along with the spid, that would simplify the proc logic. Perhaps most effective would be to pass it in as XML and use OpenXML to treat it as a table.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Yes I agree with Andy the problem is when it interprets the values they look like '1,2,3,4,5,6' and not 1,2,3,4,5,6. I would suggest usig a temp table and split you item to individual rows something like.

CREATE PROCEDURE ip_GetIt

@mv_string varchar(1000)

AS

SET NOCOUNT ON

CREATE TABLE #tempTbl (

[vals] [int] NOT NULL

)

WHILE LEN(@mv_string) > 0

BEGIN

INSERT INTO #tempTbl (vals) VALUES ( CAST((CASE WHEN CHARINDEX(',',@mv_string,1) > 0 THEN LEFT(@mv_string, CHARINDEX(',',@mv_string,1) - 1) ELSE @mv_string END) AS INT) )

SET @mv_string = CASE WHEN CHARINDEX(',',@mv_string,1) > 0 THEN RIGHT(@mv_string, LEN(@mv_string) - CHARINDEX(',',@mv_string,1)) ELSE '' END

END

SELECT *

FROM my_table

WHEN user_id IN (SELECT vals FROM #tempTbl)

DROP TABLE #tempTbl

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Eve SSCrazy Points: 2480 More actions · Answer 3

Instead of

quote:
create sp (@mv_string varchar(1000) )
as
BEGIN
select *
from my_table
where user_id in (@mv_string)

Try:

create sp (@mv_string varchar(1000) )

as

declare @sql varchar(8000)

set @sql = 'select * from my_table where user_id in ( ' + @mv_string + ')'

exec(@sql)

Go

zishanz SSCommitted Points: 1852 More actions · Answer 4

Hi, Thanks all for your help. I am also thinking about using table variable. Guess nothing simple. :=)

Abby