Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    454

    Unanswered: batch update in stored procedure

    The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @userIDs which has the following format:

    101, 102, 103

    I got the error message:

    Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

    Obviously, UserID has datatype int. How can I write this SP?


    CREATE PROCEDURE dbo.MySP
    @userIDs varchar(100)
    AS
    SET NOCOUNT ON

    BEGIN TRANSACTION

    UPDATE Users SET IsActive = 1 WHERE UserID IN (@userIDs)

    IF @@ERROR <> 0
    ROLLBACK TRANSACTION
    ELSE
    COMMIT TRANSACTION

    SET NOCOUNT OFF

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: batch update in stored procedure

    Originally posted by gyuan
    Obviously, UserID has datatype int.
    Obviousley it doesn't...

    Check this out:

    http://www.sqlteam.com/forums/topic....udf,csv,string

    Use the UDF that's posted in there
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use dynamic query - sp_executesql or execute it.

  4. #4
    Join Date
    Dec 2003
    Posts
    454

    Re: batch update in stored procedure

    Originally posted by Brett Kaiser
    Use the UDF that's posted in there
    Brett,

    I read the posters from the link you posted but I'm not very clear how to use UDF on my stored procedure since UDF is new to me. Can you give me some hint? UserID is the primary key in the table Users and its datatype is int. Thanks.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cut and paste the following code in to QA and run it...

    Code:
    USE Northwind
    GO
    
    CREATE FUNCTION CSVTable(@Str varchar(7000))
    RETURNS @t table (numberval int, stringval varchar(100), DateVal datetime)
    AS
    BEGIN
    	DECLARE @i int, @c varchar(100);
    	SELECT @Str = @Str + ',', @i = 1, @c = '';
    	WHILE @i <= LEN(@Str)
    				BEGIN
    		IF substring(@Str,@i,1) = ','
    			BEGIN
    			INSERT INTO @t(numberval, stringval, DateVal)
    			VALUES (  CASE WHEN ISNUMERIC(@c)=1 THEN @c ELSE Null END
    				, RTRIM(LTRIM(@c))
    				, CASE WHEN ISDATE(@c)=1 THEN @c ELSE Null END)
    			SET @c = ''
    			END
    		ELSE
    		
    			SET @c = @c + substring(@Str,@i,1)
    			SET @i = @i +1
    		END
    	RETURN
    END
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION SELECT 103 UNION ALL
    SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION SELECT 107 UNION ALL
    SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
    GO
    
    DECLARE @userIDs varchar(100)
    SELECT @userIDs = '101,102,103'
    
    SELECT * 
      FROM myTable99
     WHERE Col2 IN (SELECT StringVal FROM dbo.CSVTable(@userIDs))
    GO
    
    DROP FUNCTION CSVTable
    DROP TABLE myTable99
    GO
    Thank you Dr. Cross Join

    http://www.sqlteam.com/forums/pop_pr...isplay&id=6859
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    I run it and get the result I need. So now do I need to add the function to the stored procedure?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just make you're WHERE Clause look like mine....

    Just need to make sure the udf is in the same enviroment where you release the procedure...

    Get it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    Yes, it works. That is a great function. Thank you, Brett.

    By the way, can you tell me what the difference is between UDF and dynamic SQL? Performance?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, go to the link and give thanks to Jeff..

    Use dynamic sql as a last resort....and never in application code...

    I use it for admin support...but it's not released...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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