passing in a varchar

  • 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

  • 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

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

  • 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

  • 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