Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Help with this procedure

    Folks, i want to access a table by specifying values as IN list at runtime.

    create procedure return_val
    @var varchar (99)
    as
    create table #mytable(id char, name char(1))
    insert #mytable select 1,'a' union all select 2,'b' union all select 3,'c'

    select * from #mytable where id in(replace(@var,'''',''))
    drop table #mytable
    print @var
    GO
    exec return_val '1,2'

    No record is returned. Please help!

  2. #2
    Join Date
    Aug 2003
    Posts
    11
    Make this change: create table #mytable(id char(1), name char(1)). You left out the char length for the id field.

    Ignore this response. It's not correct.
    Last edited by kiloez; 08-11-04 at 14:06. Reason: Incorrect response

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS OFF 
    GO
    
    
    create function dbo.fn_ParseString2VarCharTable (
     @string	varchar(8000),
       @delimiter char(1)	  = ','				 ) returns @tbl table (
       RecordID  int identity(1,1) not null primary key clustered,
       [String]  varchar(8000)   not null		   ) as
       begin
    	  declare @spos int, @pos int
    	  --set @string = replace(replace(@string, ', ', @delimiter), ' ,', @delimiter)
    	  set @string = replace(replace(@string, @delimiter + ' ', @delimiter), ' ' + @delimiter, @delimiter)
    	  set @spos = 1
    	  set @pos = 100
    	  while (@pos) > 0 begin
    		 set @pos = charindex(@delimiter, @string, @spos)
    		 insert @tbl ([String])
    			select
    			   ltrim (
    				  rtrim (
    					 substring (
    						@string, @spos,
    						   case
    							  when (@pos - @spos) <= 0 then datalength(@string) + 1
    							  else @pos
    						   end - @spos
    					 )
    				  )
    			   )
    		 set @spos = @pos + 1
    	  end
    	  return
       end
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    
    create procedure return_val 
    @var varchar (99)
    as
    create table #mytable(id char, name char(1))
    insert #mytable select 1,'a' union all select 2,'b' union all select 3,'c'
    
    select #mytable.* from #mytable inner join dbo.fn_parsestring2varchartable(@var, ',') on id=cast(String as int)
    drop table #mytable
    print @var
    GO
    exec return_val '1,2'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Thumbs up

    Guru, thanx so much for your time.

Posting Permissions

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