Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: IN Clause and CharIndex

    Hi, The following stored procedure creates this error, "Syntax error converting the varchar value '17,18,405,408,406,493' to a column of data type int." I tried using a Convert and a Cast function but it still failed. I then read something about using the CharIndex function, but I can't figure it out. Any help would be much appreciated in getting this to work.
    Thanks

    CREATE PROCEDURE [ResourceProducts]
    @ProductHtml varchar(200)
    AS

    SELECT Resources.ResourceId, Resources.Heading
    FROM Resources
    INNER JOIN Products
    ON Resources.ResourceId IN (Products.Resources)
    Where Products.ProductHtml = @ProductHtml
    GROUP BY Resources.Heading, Resources.ResourceId
    GO

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Two things:

    1. Change your INNER JOIN clause to read as follows:

    INNER JOIN Products ON Resources.ResourceId = Products.Resources

    2. Create a function that will return a table that contains the values that you passed in @ProductHtml and INNER JOIN it.

    Below is not the best, but a working version of this function. I am sure someone may come in and offer a more optimal approach.

    Code:
    create function dbo.fn_ParseStringToIntValues (
       @str varchar(8000) ) returns @tbl table (IntValue int not null)
    as begin
       declare @pos int
       
       while charindex(',', @str, 1) > 0 begin
          set @pos = charindex(',', @str, 1)
          insert @tbl values (cast(substring(@str, 1, @pos-1) as int))
          set @str = substring(@str, @pos+1, datalength(@str)-@pos)
       end
       insert @tbl values (cast(@str as int))
       return
       end

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...Oh, and the resulting procedure will look like this:

    CREATE PROCEDURE [ResourceProducts]
    @ProductHtml varchar(200)
    AS

    SELECT Resources.ResourceId, Resources.Heading
    FROM Resources
    INNER JOIN Products ON Resources.ResourceId = Products.Resources
    INNER JOIN dbo.fn_ParseStringToIntValues(@ProductHtml) f
    on Products.ProductHtml = f.IntValue
    GROUP BY Resources.Heading, Resources.ResourceId
    GO

  4. #4
    Join Date
    Sep 2003
    Posts
    4

    hmmm, is there not a more simple way.

    The database field "Products.Resources" is the comma list not @ProductHtml. I know its not good to have a db setup with a comma list as an entry, but the db was set up that way long ago and I can't update it.

    So how would this work? I've heard its as easy as adding this CharIndex clause...appreciate the help.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, then it'll look like this:

    CREATE PROCEDURE [ResourceProducts]
    @ProductHtml varchar(200)
    AS
    declare @Resources varchar(8000)
    select @Resources = Resources
    from Products where ProductHtml = @ProductHtml

    SELECT Resources.ResourceId, Resources.Heading
    FROM Resources
    inner join dbo.fn_ParseStringToIntValues(@Resources) f
    on Resources.ResourceId = f.IntValue
    GROUP BY Resources.Heading, Resources.ResourceId
    GO

  6. #6
    Join Date
    Sep 2003
    Posts
    4

    Wink

    Thanks for all your help, however after messing around with this bad table design I decided to re-do that part of the db the correct way and add a junction table.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •