CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    65

    Post Passing value to the parameter "IN (@par)"

    I have wrote a stored procedure which like


    CREATE PROCEDURE P1
    (
    @par1 string,

    )
    as

    select * from table1 where column1 in (@par1)

    So, here the Value which i send to the parameter is "value1,value2" like.
    Its not possible for me to pass the parameter.

    Could you suggest how to do this with the same way or different way.

    Thanks in advance,

    Regards,
    Sivakumar.kr.
    Edit/Delete Message
    Sivakumar Kuduva Ramamoorthy

  2. #2
    Join Date
    Feb 2005
    Location
    Denmark
    Posts
    742

    Re: Passing value to the parameter "IN (@par)"

    You do not specify which database it is, but for SQL Server there is a way of doing this - but it isn't "pretty", so I would much rather advice you to rework the method.

    However, basically - you need to execute the a string so you can parse in a comma separated string.

    Something along this line:
    Code:
    CREATE PROCEDURE P1
    (
    @par1 varchar(max)
    )
    as begin
    
    exec('select * from table1 where column1 in (' + @par1 + ')')
    
    end
    And then call the sproc with an input string which looks something like:
    Code:
    EXEC P1 '''value1'',''value2'''
    But it isn't a "pretty" way of doing this, and I wouldn't really recommend doing it.

  3. #3
    Join Date
    Oct 2004
    Posts
    65

    Arrow Re: Passing value to the parameter "IN (@par)"

    Thanks it works fine.
    Sivakumar Kuduva Ramamoorthy

  4. #4
    Join Date
    Sep 2006
    Posts
    95

    Re: Passing value to the parameter "IN (@par)"

    Best way is to use a function that turns a delimited string into a table, and in your sp, fill a temp table with your values, and use this temp table in your where clause, or as inner join in your query.
    here's a function to turn a bunch of parameters into a table.
    Code:
    CREATE FUNCTION dbo.fnSplit (	
    @vcDelimitedString 	varchar(8000),
    @vcDelimiter	varchar(100) )
    
    RETURNS @tblArray TABLE 
     (
    	ElementID	smallint	IDENTITY(1,1), --Array index
     	Element		varchar(1000)			--Array element contents
     ) AS
    BEGIN
    	DECLARE 
    	@siIndex					smallint,
    	@siStart					smallint,
    	@siDelSize					smallint
    	SET @siDelSize	= LEN(@vcDelimiter)
    	WHILE LEN(@vcDelimitedString) > 0
    	BEGIN
    		SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
    		IF @siIndex = 0
    		BEGIN
    			INSERT INTO @tblArray VALUES(@vcDelimitedString)
    			BREAK
    		END
    		ELSE
    		BEGIN
    			INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
    			SET @siStart = @siIndex + @siDelSize
    			SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
    		END
    	END
    	
    	RETURN
    END
    On Error Kill(User)

  5. #5
    Join Date
    Dec 2003
    Location
    Northern Ireland
    Posts
    1,362

    Re: Passing value to the parameter "IN (@par)"

    Which dbms are you using?

    If mysql, there is a function called Find_In_Set() which works with a delimited string.
    Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook


    0100 1101 0110 1001 0110 0011 0110 1000 0110 0001 0110 0101 0110 1100 0010 0000 0100 0101 0110 1100 0110 1100 0110 0101 0111 0010

  6. #6
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Passing value to the parameter "IN (@par)"

    you can also turn it around:

    SELECT * FROM table WHERE @par LIKE '%,'+ column+',%'


    Pass a string like:

    ,123,456,789,
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured