Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Unanswered: Passing values to a stored procedure

    I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :


    create procedure SelectDetails
    @Id string
    as
    Select * from DtTable where itemid in(@Id)


    Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
    How can i solve this problem?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Code:
    Create Proc My Proc
    @myListOfIDS varchar(100)
    AS
    
    DECLARE @valTable TABLE (My_ID VARCHAR(10))
    
    DECLARE  @ListValueID VARCHAR(10),@Pos INT                              
    
    SET @myListOfIDS = @myListOfIDS + ','                                                      
    
    SET @Pos = CHARINDEX(',',@myListOfIDS,1)  
    IF REPLACE(@myListOfIDS,',','') <> ''      
    BEGIN        
    	WHILE @Pos > 0          
    	BEGIN            
    		SET @ListValueID = LTRIM(RTRIM(LEFT(@myListOfIDS,@Pos - 1)))                                           
    		IF @ListValueID <> ''              
    		BEGIN                
    			INSERT INTO @valTable(My_ID)                
    			VALUES(@ListValueID)             
    		END                          
    		
    		SET @myListOfIDS = RIGHT(@myListOfIDS,LEN(@myListOfIDS) - @Pos)                                          
    		SET @Pos = CHARINDEX(',',@myListOfIDS,1)          
    	END      
    END
    
    Select DtTable.col1,DtTable.col2 
    from DtTable 
    JOIN @valTable
    ON DtTable.itemid = @valTable.My_ID
    “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.

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    Thanx for the reply.
    But the problem is that this is used in a report. So many users will b trying to access this report. So if we r trying to insert the values into a temporary table it will create problem.
    So is there any other way.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Number 1. That is not a temp table, it is a table variable that is unique to the session.

    Number 2. You would not have that problem with temp tables because temp tables declared with one pound sign (#temptable) are also unique to the session that opens them.

    that will work just fine.
    “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.

  5. #5
    Join Date
    Mar 2008
    Posts
    3
    Thanx for the help. It worked!

Posting Permissions

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