Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    21

    Unanswered: Passing multiple values to a SQL stored procedure

    Hi,

    I have created a SQL stored procedure in SQL 2005 standard edition. Please see the code below.

    Currently I can only pass 1 value for component ID at once and have it generate my result.

    I am wondering if it is possible, and how to get it to except mulitple component ID's at once and return all results to me.


    ALTER proc [dbo].[pr_get_root_node3](@COMPONENT varchar(80)) as

    declare @table table(node varchar(80),parent_node varchar(80))

    insert into @table(node, parent_node)
    select COMPONENT ,PART
    from BOM01
    where COMPONENT = @COMPONENT

    while(1=1)
    begin
    insert into @table(node, parent_node)
    select COMPONENT ,PART
    from BOM01
    where COMPONENT in (select parent_node from @table)
    and COMPONENT not in (select node from @table)

    if (@@rowcount = 0)
    break
    end

    select parent_node, @COMPONENT
    from @table
    where parent_node not in (select node from @table)

    Any help is greatly appreciated.
    Thanks,
    Michiel

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Form an XML on the front-end that is comprised of multiple component ID's, and pass it in XML parameter. In the procedure use XQuery syntax to form a set that you can use in regular queries.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    there is this old gem . pass in a string of values, create and populate a table variable and join to that table variable. Some code I clipped from someone...

    Code:
    DECLARE @ListValues VARCHAR(5000)
    SET @ListValues = ('1223,1445,1998')
    
    DECLARE @valTable TABLE(UID VARCHAR(10))
    
    DECLARE  @ListValueID VARCHAR(10),@Pos INT                              
    
    SET @ListValues = LTRIM(RTRIM(@ListValues)) + ','                                                      
    
    SET @Pos = CHARINDEX(',',@ListValues,1)                   
    
    IF REPLACE(@ListValues,',','') <> ''      
    BEGIN        
    	WHILE @Pos > 0          
    	BEGIN            
    		SET @ListValueID = LTRIM(RTRIM(LEFT(@ListValues,@Pos - 1)))                                           
    		IF @ListValueID <> ''              
    		BEGIN                
    			INSERT INTO @valTable(UID)                
    			VALUES(@ListValueID) --Use Appropriate conversion              
    		END                          
    		
    		SET @ListValues = RIGHT(@ListValues,LEN(@ListValues) - @Pos)                                          
    		SET @Pos = CHARINDEX(',',@ListValues,1)          
    	END      
    END
    
    SELECT * FROM @valTable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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