Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: SP in SQL 2000 w/ In Clause

    Stupid question, I'm sure, as I'm new to Stored Procedures. In the SP below, I'm passing a value to the NewsID which contains a list of numbers (e.g., 67,78,34). For whatever reason, it takes the first number (not the first number in the list, but the first number...i.e., 6) and uses it. I'm sure there's a basic syntax issue here, but couldn't find anything in books online or in the forums. Help's appreciated.

    CREATE PROCEDURE get_News (
    @begin smalldatetime,
    @end smalldatetime,
    @NewsID varchar)

    AS
    BEGIN

    SELECT DISTINCT CAST(a.News_ID AS smallint) AS News_ID, a.Title, a.URL, b.Name, c.Cat_ID, c.Sub_ID, a.TStamp
    FROM News a INNER JOIN
    News_Source b ON a.Source = b.ID INNER JOIN
    News_Category_rel c ON a.News_ID = c.News_ID
    WHERE (a.TStamp > @begin) AND (a.TStamp < @end) AND (cast(c.Cat_ID AS varchar) IN (@NewsID))

    END
    GO

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    you can implement the following function:

    Code:
    if object_id('dbo.fn_ParseStringToIntValues') is not null
       drop function dbo.fn_ParseStringToIntValues
    go
    
    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
    go
    then you'll re-write your procedure like this:

    Code:
    CREATE PROCEDURE get_News (
       @begin smalldatetime,
       @end smalldatetime,
       @NewsID varchar(8000))
    AS
       SELECT DISTINCT CAST(a.News_ID AS smallint) AS News_ID, a.Title, a.URL, b.Name, c.Cat_ID, c.Sub_ID, a.TStamp
          FROM News a 
          INNER JOIN News_Source b ON a.Source = b.ID
          INNER JOIN News_Category_rel c ON a.News_ID = c.News_ID
          inner join dbo.fn_ParseStringToIntValues(@NewsID) f on c.Cat_ID = f.IntValue
          WHERE (a.TStamp > @begin) AND (a.TStamp < @end) --AND (cast(c.Cat_ID AS varchar) IN (@NewsID))
    go

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    Thumbs up Thanks so much.

    That worked well. Much appreciated.

Posting Permissions

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