locked
How to update an ID using a stored procedure in table #1 RRS feed

  • Question

  • Environment: Win7 and SQL server 2008 R@

    Tools: SQL management tool 2008 R2

    T-SQL code - works fine- :

    DECLARE	@return_value int, @new_id char(9)
    EXEC	@return_value = [dbo].[proc_gen_id]
    
    @new_id = @new_id OUTPUT
    SELECT	@new_id as N'@new_id'


    Outcome - for example-: 433194976 or 907959111

    T-SQL code

    CREATE TABLE employees
    ( 
      id char(9),
      employee_number number(10) not null,
      employee_name varchar2(50) not null,
      salary number(6),
      CONSTRAINT employees_pk PRIMARY KEY (employee_number)
    );
    
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1001, 'Sally Johnson', 32000); 
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1002, 'Will Smith', 38000); 
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1003, 'Jessica simpson', 32000); 
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1004, 'Michael Lira', 65000); 


    Problem:

    How I would update an id column, which is generated/called by the stored procedure

    My code

    DECLARE @return_value int, @new_id char(9) EXEC @return_value = [dbo].[proc_gen_id] @new_id = @new_id OUTPUT SELECT @new_id as N'@new_id' UPDATE Employee Set id=@new_id

    Before

    ('null',1001, 'Sally Johnson', 32000);
    ('null',1002, 'Will Smith', 38000);
    ('null',1003, 'Jessica simpson', 32000);
    ('null',1004, 'Michael Lira', 65000);

    After:Expected results

    ('433194976',1001, 'Sally Johnson', 32000);
    ('907959111',1002, 'Will Smith', 38000);
    ('819753931',1003, 'Jessica simpson', 32000);
    ('000753931',1004, 'Michael Lira', 65000);

    Please help








    • Edited by Sandra VO Wednesday, February 19, 2014 7:11 PM
    Tuesday, February 18, 2014 11:42 PM

Answers

  • You may try the below: (Its just a work around.But there is no guarantee of non-duplicates though.I would suggest you to consider a IDENTITY/SEQUENCE rather than a custom method)

    CREATE TABLE employees1
    ( 
      id char(9),
      employee_number int not null,
      employee_name varchar(50) not null,
      salary int,
      CONSTRAINT employees_pk PRIMARY KEY (employee_number)
    );
    
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1001, 'Sally Johnson', 32000); 
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1002, 'Will Smith', 38000); 
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1003, 'Jessica simpson', 32000); 
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1004, 'Michael Lira', 65000); 
    
    Select * From employees1
    
    update a Set Id= (Select [dbo].[fn_gen_id]())
    From employees1 a
    
    Select * From employees1
    
    --You may need to create the below objects
    /*
    Create VIEW vwRand
    AS
    	Select Right('000000000'+LTRIM(RTRIM(STR(RAND()*1000000000))),9) as Rand
    
    Alter function dbo.[fn_gen_id]()
    RETURNS int
     as
        begin
        DECLARE @RETURN int
        DECLARE @Upper INT;
        DECLARE @Lower INT;
        DECLARE @Random float;
    
        SELECT @Random = Rand
        FROM vwRand
     return @Random
     end;
     */
     
     

    • Marked as answer by Sandra VO Wednesday, February 19, 2014 6:34 PM
    Wednesday, February 19, 2014 5:26 AM

All replies

  • Rather than stored procedure, it would be better to use function. During what situation you want to update? Generally you can do insert via stored procedure or write a trigger on the table certain column of same row.

    Just for my curiosity, what's the purpose of ID column?


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, February 19, 2014 12:17 AM
  • I loaded huge volume of data from an external source using SSIS. Loading this data takes place in a bi-weekely basis. The purpose of ID column is to accommodate additional elements and to associate SSN with the ID column. Let us assume that am using function. how i would use the function to insert a new column with a values or update.  

    Wednesday, February 19, 2014 12:27 AM
  • Interesting.

    1. What is the input for the stored procedure/function? I don't see any.
    2. Does it has any relationship with the other columns? I guess it should, if yes, then that has to go as input parameter to # 1 

    In short, What's the co-relation of Id column with that particular row? Is it a random number for that row .. like GUID?

    If you can get a function, you have specify that during insert/update statement as below

    USE [AdventureWorks2012]
    GO
    
    INSERT INTO [HumanResources].[Department]
               ([Name]
               ,[GroupName])
         VALUES
               ([dbo].[ufnGetPurchaseOrderStatusText](1)
               ,'SQLServer-Help.com')
              
    GO
    
    	


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, February 19, 2014 12:39 AM
  • The stored procedure [dbo].[proc_gen_id] returns generated random number such as 819753931 or 433194976 per one execution. There is no input value to be passed 

    I see how to call UDF using INSERT statment but the problem am dealing with 1000s of records. How I would update all records without the need to go manually to update them

    for example, The table has the following values

    ('null',1001, 'Sally Johnson', 32000);
    ('null',1002, 'Will Smith', 38000);
    ('null',1003, 'Jessica simpson', 32000);
    ('null',1004, 'Michael Lira', 65000);

    after using stored procedure or function the expected data should look like

    ('433194976',1001, 'Sally Johnson', 32000);
    ('907959111',1002, 'Will Smith', 38000);
    ('819753931',1003, 'Jessica simpson', 32000);
    ('000753931',1004, 'Michael Lira', 65000);

    I hope this clear


    • Edited by Sandra VO Wednesday, February 19, 2014 1:03 AM
    Wednesday, February 19, 2014 1:02 AM
  • Try the below:

    CREATE TABLE employees
    ( 
      id char(9),
      employee_number int not null,
      employee_name varchar(50) not null,
      salary int,
      CONSTRAINT employees_pk PRIMARY KEY (employee_number)
    );
    
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1001, 'Sally Johnson', 32000); 
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1002, 'Will Smith', 38000); 
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1003, 'Jessica simpson', 32000); 
    INSERT INTO employees (employee_number, employee_name, salary) VALUES (1004, 'Michael Lira', 65000); 
    
    Select * From employees
    
    update a Set Id= (Select [dbo].[fn_gen_id]())
    From employees a
    
    
    Select * From employees
    
    --You may need to create the below objects
    /*
    
    CREATE VIEW vwRand
    AS
    SELECT RAND() as Rand
    
    Alter function dbo.[fn_gen_id]()
    RETURNS int
     as
        begin
        DECLARE @RETURN int
        DECLARE @Upper INT;
        DECLARE @Lower INT;
        DECLARE @Random float;
    
        SELECT @Random = Rand
        FROM vwRand
    
          SET @Lower = 1000 
          SET @Upper = 9999 
          set @RETURN= (ROUND(((@Upper - @Lower -1) * @Random + @Lower), 0))
    
     return @RETURN
     end;*/

    • Proposed as answer by Naomi N Wednesday, February 19, 2014 1:34 AM
    Wednesday, February 19, 2014 1:20 AM
  • As Balmukund said, you need to have a function in order to update all rows. Otherwise you would need to do one row at a time, e.g. loop through your table and call this procedure for every row.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 1:20 AM
  • I tried testing update via UDF but looks like i am facing some error there.

    Can you please share your function code?


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, February 19, 2014 1:22 AM
  • question: does the function return 9 char length for ID

    Wednesday, February 19, 2014 1:25 AM
  • I likeed Latheesh's approach but it all depends on the function which you are using.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, February 19, 2014 1:25 AM
  • BEGIN
    	DECLARE
    		@new_nbr	CHAR(9),
    		@user_cnt	INT,
    		@cnt 		INT,
    		@len		INT
    
    	SELECT	@cnt = 1
    
    	WHILE( @cnt = 1)
    	BEGIN
    SELECT	@new_nbr = convert(CHAR(9),convert(int,(RAND()*1000000000)))
    
    		SELECT @len = datalength(ltrim(rtrim(@new_nbr)))
    
    		WHILE (@len < 9)
    			BEGIN
    				SELECT @new_nbr = '0'+@new_nbr
    				SELECT @len = @len +1
    			END

    Wednesday, February 19, 2014 1:31 AM
  • As Balmukund said, you need to have a function in order to update all rows. Otherwise you would need to do one row at a time, e.g. loop through your table and call this procedure for every row.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    will be kind to show me how would you update one row at a time by using loop for example...am really curious to see and learn from you. Thanks Noami
    Wednesday, February 19, 2014 1:33 AM
  • You can not use RAND() function in a function.

    Check this blog post explaining a workaround

    http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 1:33 AM
  • Thanks Noami. let us assume that the work around has been done. how to update the entire table. how would you update one row at a time by using loop for example
    Wednesday, February 19, 2014 1:56 AM
  • Thanks Noami. let us assume that the work around has been done. how to update the entire table. how would you update one row at a time by using loop for example
    Did you get a chance to look at my sample method? Any issues with your actual scenario?
    Wednesday, February 19, 2014 1:57 AM
  • What is your new function and does it take any parameter?

    update Employees set id = dbo.myRandFunction();

    (this is assuming we have a scalar function that doesn't need a parameter)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 2:07 AM
  • You may try the below: (Its just a work around.But there is no guarantee of non-duplicates though.I would suggest you to consider a IDENTITY/SEQUENCE rather than a custom method)

    CREATE TABLE employees1
    ( 
      id char(9),
      employee_number int not null,
      employee_name varchar(50) not null,
      salary int,
      CONSTRAINT employees_pk PRIMARY KEY (employee_number)
    );
    
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1001, 'Sally Johnson', 32000); 
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1002, 'Will Smith', 38000); 
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1003, 'Jessica simpson', 32000); 
    INSERT INTO employees1 (employee_number, employee_name, salary) VALUES (1004, 'Michael Lira', 65000); 
    
    Select * From employees1
    
    update a Set Id= (Select [dbo].[fn_gen_id]())
    From employees1 a
    
    Select * From employees1
    
    --You may need to create the below objects
    /*
    Create VIEW vwRand
    AS
    	Select Right('000000000'+LTRIM(RTRIM(STR(RAND()*1000000000))),9) as Rand
    
    Alter function dbo.[fn_gen_id]()
    RETURNS int
     as
        begin
        DECLARE @RETURN int
        DECLARE @Upper INT;
        DECLARE @Lower INT;
        DECLARE @Random float;
    
        SELECT @Random = Rand
        FROM vwRand
     return @Random
     end;
     */
     
     

    • Marked as answer by Sandra VO Wednesday, February 19, 2014 6:34 PM
    Wednesday, February 19, 2014 5:26 AM
  • I had the same problem with this thread but now the messages re-appeared (after you added that message).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 4:35 PM
  • Latheesh: dont worry about the duplicates. My goal is to undersatnd the concept. I am testing out the code and let you know

    Noami: Yes, a scalar function that doesn't need a parameter but it returns value for each row

    
    Wednesday, February 19, 2014 4:42 PM
  • Then you can use that function in INSERT and UPDATE (I showed the UPDATE usage)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 4:43 PM
  • The following code presents errors

    Create VIEW vwRand
    AS
    	Select Right('000000000'+LTRIM(RTRIM(STR(RAND()*1000000000))),9) as Rand
    
    Create function dbo.[fn_gen_id]()
    RETURNS int
     as
        begin
        DECLARE @RETURN int
        DECLARE @Upper INT;
        DECLARE @Lower INT;
        DECLARE @Random float;
    
        SELECT @Random = Rand
        FROM vwRand
     return @Random
     end;
     

    Msg 156, Level 15, State 1, Procedure vwRand, Line 5
    Incorrect syntax near the keyword 'Create'.
    Msg 111, Level 15, State 1, Procedure vwRand, Line 5
    'CREATE FUNCTION' must be the first statement in a query batch.
    Msg 178, Level 15, State 1, Procedure vwRand, Line 16
    A RETURN statement with a return value cannot be used in this context.

    Wednesday, February 19, 2014 4:52 PM
  • Sandra,

    Execute the CREATE VIEW and CREATE FUNCTION separately. Or with GO separator.

    First execute the below:

    Create VIEW vwRand
    AS
    	Select Right('000000000'+LTRIM(RTRIM(STR(RAND()*1000000000))),9) as Rand

    Then the below:

    Create function dbo.[fn_gen_id]()
    RETURNS int
     as
        begin
        DECLARE @RETURN int
        DECLARE @Upper INT;
        DECLARE @Lower INT;
        DECLARE @Random float;
    
        SELECT @Random = Rand
        FROM vwRand
     return @Random
     end;

    Wednesday, February 19, 2014 4:55 PM
  • Everything works as expected except one thing

    the ID number is generated as  below

    237690680
    91828594

    348619550
    56715991

    The ID length must be 9

    how we can solve this

    Wednesday, February 19, 2014 5:06 PM
  • You may change the function as below:

    Alter function dbo.[fn_gen_id]()
    RETURNS varchar(9)
     as
        begin
        DECLARE @Random varchar(9);
    
        SELECT @Random = Rand
        FROM vwRand
     return @Random
     end;

    Wednesday, February 19, 2014 5:13 PM
  • Thanks. let me see
    Wednesday, February 19, 2014 5:14 PM
  • Instead of varchar(9) use char(9) (to match the column also). Then the number will be padded with spaces at the end if it's less than 9 digits.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 5:16 PM
  • Instead of varchar(9) use char(9) (to match the column also). Then the number will be padded with spaces at the end if it's less than 9 digits.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Hi Naomi, But Sandra was looking for data with 0 prefixed for less number of 9 characters.
    Wednesday, February 19, 2014 5:19 PM
  • Right. I checked the view code - it will return a character number with 0 in front. I think we can use CAST in the view as well. Her original code was using float (which was wrong since the view was returning character).

    Getting rid of extra variables and switching to varchar(9) or char(9) will solve the problem.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, February 19, 2014 5:21 PM
  • In reference to"looking for data with 0 prefixed for less number of 9 characters."

    The  ID number must be 9 digits (Len(id) must be 9). The data type is char to match.


    I also not necessary looking for data with prefixed 0 they have to be 9. those ID will be assigned to the row in the table.
    • Edited by Sandra VO Wednesday, February 19, 2014 5:31 PM
    Wednesday, February 19, 2014 5:30 PM
  • Thanks Naomi and thanks for Latheesh. 

    Wednesday, February 19, 2014 6:33 PM