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

    Unanswered: using a where in select in a stored proc

    i have a vb app that is retriving data from an sql db using ado. i have a qry save in my db. i want to select from this qry where x in (1,2,3,4). i don't know how many values i will be putting into my where in statment. it could be one value or 100 values. below is my code. what i would like to do is pass one paramater to my stored proc and then break it up and use it in my select.
    my desired result is as follows

    -----------------------------------------------
    declare cnt int
    declare TempFulLString nvarchar(5000)
    declare TempStyleFID int

    TempFulLString = @str_TempString

    select * from QryPicking_Slip_Fill_Listview1 where stylefid in (

    While TempFulLString <> ''
    begin

    cnt = InStr(1, TempFulLString, ',')
    TempStyleFID = Left(TempFulLString, (cnt))
    TempFulLString = Right(TempFulLString, (Len(TempFulLString) - cnt))
    TempStyleFID + ','

    end
    )
    -----------------------------------------------

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I suspect that you might want to use a function, something like:
    PHP Code:
    --  ptp  20040310  Split an argument into a table of values for use in stored procedures

    CREATE 
    FUNCTION dbo.fList(@pcList VARCHAR(8000), @pcDelimiter VARCHAR(20) = ',')
    RETURNS @tResult TABLE (resultId INT IDENTITYvalue VARCHAR(80))
    AS 
    BEGIN

    DECLARE @i    INT

    SELECT 
    @CharIndex(@pcDelimiter, @pcList)

       WHILE 
    < @i
          BEGIN
             INSERT 
    @tResult (valueSELECT Left(@pcList, @1)
             
    SELECT @pcList SubString(@pcList, @Len(@pcDelimiter), 8000)
             
    SELECT @CharIndex(@pcDelimiter, @pcList)
          
    END

       INSERT 
    @tResult(valueVALUES (@pcList)

       RETURN
    END 
    You can then call the function, and if needed CAST the value however you need it, something like:
    PHP Code:
    SELECT *, CAST (value AS MONEYFROM dbo.fList('1, 2, 3, 4, 52'','
    This will allow you to take an argument list of arbitrary size and make it into whatever datatype suits you.

    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    29
    does this function fill a temp table and then i would run a regular stored proc using my qry with a join to this new temp table.

    let me try to explain what i want to do, i want to pass the following string to my stored proc "123,456,789," (this string can grow). i then want to break the string up into the following values "123", "456", "789". i would then like to use these 3 values in the following select statment
    select * from table1 where tempval in (123, 456, 789)

    please advise.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This function actually returns a table with the values from the list that you pass it. Your example would look like:
    PHP Code:
    SELECT b.*
       
    FROM dbo.fList('123,456,789'',') AS a
       JOIN dbo
    .table1 AS b
          ON 
    (b.tempval CAST (a.value AS INT)) 
    It would return all of the rows from table1 where the value of tempval was 123 or 456 or 789.

    -PatP

Posting Permissions

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