April 28, 2002 at 5:59 pm
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
April 28, 2002 at 6:06 pm
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
April 29, 2002 at 4:54 am
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)
April 29, 2002 at 7:29 am
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
April 29, 2002 at 1:09 pm
Hi, Thanks all for your help. I am also thinking about using table variable. Guess nothing simple. :=)
Abby
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply