Post reply

Simple query format

  • April 14, 2005 at 6:46 pm

    #68969

    How to make this work to select id = 1234 or 2345 or 4444.

    I have to pass these values from somewhere else so I need to do this way.

    I tried putting "'" before the string after the string but not working there is

    something simple I'm missing here i believe.

    DECLARE @test-2 NVARCHAR(250)

    SET @test-2 = '1234' , '2345' , '4444'

    SELECT * FROM TABLE1

    WHERE ID IN (@TEST)

    Thanks

    ash

  • April 14, 2005 at 8:47 pm

    #552840

    DECLARE @test-2 NVARCHAR(250)

    SET @test-2 = '1234 , 2345 , 4444'

    exec('SELECT * FROM TABLE1 WHERE ID IN (' + @test-2 + ')')

  • April 14, 2005 at 10:31 pm

    #552842

    Or you could use one of the many string splitting functions that are floating around.

    Here's one,

    CREATE FUNCTION dbo.udf_SplitChar (@vcrStrAry varchar(8000), @chrDelim char(1))
RETURNS @Results TABLE ( Items varchar(8000) )
AS
BEGIN
 DECLARE @intPos int
 DECLARE @vcrItem varchar(8000)
 -- Get the position of the first delimiter
 SELECT @intPos = CHARINDEX(@chrDelim, @vcrStrAry)
 WHILE @intPos <> 0
 BEGIN 
  -- Get the item from the string
  SELECT @vcrItem = LEFT(@vcrStrAry, @intPos - 1)
  -- Put the item into the results set
  INSERT INTO @Results(Items) VALUES(@vcrItem)
  -- Remove the item from the passed string
  SELECT @vcrStrAry = RIGHT(@vcrStrAry, LEN(@vcrStrAry) - @intPos)
  -- Get the new position of the delimiter in the new string
  SELECT @intPos = CHARINDEX(@chrDelim, @vcrStrAry)
 END
 -- Check if there is anything left of the string
 IF LEN(@vcrStrAry) > 0
  -- Put the last item into the results set
  INSERT INTO @Results(Items) VALUES(@vcrStrAry)
  RETURN
END

    Then to use the function you run,

    DECLARE @TEST NVARCHAR(250)
    SET @TEST = '1234,2345,4444'

SELECT * FROM TABLE1 
INNER JOIN [dbo].[udf_SplitChar](@TEST, ',') lst 
ON TABLE1.ID = lst.Item

     

    --------------------
    Colt 45 - the original point and click interface

  • April 16, 2005 at 5:54 pm

    #553055

    Thanks for your help guys.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply