none
Using Stored Procedure to insert records

    Question

  • As I need to insert more than 100 rows of record into a table, the following Stored Procedure had been written which neither insert any record nor produce any error:

    CREATE PROCEDURE insert_Equipment
    (
    	@equip_name VARCHAR(20),
    	@num INTEGER
    )
    AS
    BEGIN
    	SET @num = 6053;
    	SET @equip_name = 'Computer';
    	WHILE (@num <= 6177)
    	BEGIN
    		INSERT INTO Equipment VALUES ('eq'+@num, @equip_name);
    		SET @num = @num + 1;
    	END
    END

    Saturday, June 02, 2012 12:38 PM

Answers

  • Hi try this

    CREATE PROCEDURE insert_Equipment
    (
    	@equip_name VARCHAR(20),
    	@num INTEGER
    )
    AS
    BEGIN
    	SET @num = 6053;
    	SET @equip_name = 'Computer';
    	WHILE (@num <= 6177)
    	BEGIN
    		INSERT INTO Equipment VALUES ('eq'+Cast(@num As Varchar(5)), @equip_name);
    		SET @num = @num + 1;
    	END
    END


    PS.Shakeer Hussain

    • Marked as answer by favourGreen Monday, June 04, 2012 1:10 PM
    Saturday, June 02, 2012 3:40 PM

All replies

  • Hi,

    Error is in Insert Statement because your are trying to insert AlphaNumeric value in Integer column.

    eg: eq6053

    So remove eq in insert statement

    INSERT INTO Equipment VALUES (@num, @equip_name);


    PS.Shakeer Hussain

    Saturday, June 02, 2012 12:53 PM
  •   INSERT INTO Equipment VALUES ('eq'+@num, @equip_name)


    No wonder; there isn't defined which data should be inserted; at all, the statement makes no sense.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Saturday, June 02, 2012 12:54 PM
  • Are you sure it is not throwing an error "Conversion failed when converting the varchar value 'eq' to data type int."

    and also share the DDL for your Table


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Saturday, June 02, 2012 12:56 PM
  • I tried to convert the @count variable to VARCHAR and append it to 'eq' so as to obtain the equip_code. However, it still doesn't work.

    Would anyone has a better idea of making this work?

    CREATE PROCEDURE insert_Equipment
    (
    	@equip_code VARCHAR(6),
    	@equip_name VARCHAR(20),
    	@count INTEGER,
    	@str_code VARCHAR(4)
    )
    AS
    BEGIN
    	SET @count = 6053;
    	SET @str_code = CAST(@count AS VARCHAR);
    	SET @equip_code = 'eq' + @str_code;
    	SET @equip_name = 'Computer';
    	WHILE (@count <= 6177)
    	BEGIN
    		INSERT INTO Equipment VALUES (@equip_code, @equip_name);
    		SET @count = @count + 1;
    	END
    END

    Saturday, June 02, 2012 1:18 PM
  • Hi,

    Check the datatype of a column @equip_code in a table.if the column is integer it will not insert.


    PS.Shakeer Hussain

    Saturday, June 02, 2012 1:20 PM
  • equip_code is definitely a VARCHAR in the table definition.

    CREATE TABLE Equipment
    (
    equip_no VARCHAR(6),
    equip_type VARCHAR(20) NOT NULL
    CONSTRAINT equip_pk PRIMARY KEY (equip_no),
    CONSTRAINT equip_fk FOREIGN KEY (equip_type) REFERENCES Equipment_Details
    )

    Saturday, June 02, 2012 1:29 PM
  • Hi,

    Try this

    CREATE PROCEDURE insert_Equipment ( @equip_code VARCHAR(6), @equip_name VARCHAR(20), @count INTEGER, @str_code VARCHAR(4) ) AS BEGIN SET @count = 6053; SET @equip_name = 'Computer'; WHILE (@count <= 6177) BEGIN

    SET @str_code = CAST(@count AS VARCHAR(4)); SET @equip_code = 'eq' + @str_code;

    INSERT INTO Equipment VALUES (@equip_code, @equip_name); SET @count = @count + 1; END END


    PS.Shakeer Hussain

    Saturday, June 02, 2012 1:50 PM
  • Afte revising the code to the above, it still doesn't work.

    Saturday, June 02, 2012 2:01 PM
  • First, below code works fine for me

    create table Equipment
    (
    	equip_name VARCHAR(20),
    	num INTEGER
    )
    go
    
    CREATE PROCEDURE insert_Equipment
    (
    	@equip_name VARCHAR(20),
    	@num INTEGER
    )
    AS
    BEGIN
    	SET @num = 6053;
    	SET @equip_name = 'Computer';
    	WHILE (@num <= 6177)
    	BEGIN
    		INSERT INTO Equipment VALUES (@equip_name, @num);
    		SET @num = @num + 1;
    	END
    END
    go
    
    exec insert_Equipment '',0
    select * from Equipment
    --125 rows selected
    
    drop table Equipment;
    drop procedure insert_Equipment
    go

    Next, why you need parameteres if the proc make no use of them as parameters?

    Serg

    Saturday, June 02, 2012 2:27 PM
  • Afte revising the code to the above, it still doesn't work.

    And what "doesn't work" mean? Do you get unexpected results? Any error messages you are keeping secret? Something else? Exactly how do you call the procedure?

    I like to point out that not providing a column list in an INSERT statement is not good practice. This code:

    INSERT INTO Equipment VALUES (@equip_code, @equip_name)

    should read:

    INSERT INTO Equipment(equip_no, equip_type)
        VALUES (@equip_code, @equip_name)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 02, 2012 2:31 PM
  • Although I don't understand the reason behind declaring the variables as parameters in all the code example above, the above code by Syed Shakeer Hussain should still work.

    favourGreen, could you show us how are you executing the stored proc? 


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Saturday, June 02, 2012 2:51 PM
  • I've made the stored proc more general by removing the hard coded values so that you just pass parameters.

    -- I've tried to create a dummy referenced table
    CREATE TABLE Equipment_Details (id INT, equip_type VARCHAR(20) PRIMARY KEY)
    INSERT INTO Equipment_Details VALUES (1, 'Computer')
    INSERT INTO Equipment_Details VALUES (2, 'Laptop')
    GO
    -- This is the DDL given by you
    CREATE TABLE Equipment
    (
    equip_no VARCHAR(6),
    equip_type VARCHAR(20) NOT NULL
    CONSTRAINT equip_pk PRIMARY KEY (equip_no),
    CONSTRAINT equip_fk FOREIGN KEY (equip_type) REFERENCES Equipment_Details
    )
    GO
    -- Verify data
    SELECT * FROM Equipment_Details
    SELECT * FROM Equipment
    GO
    -- Create the procedure
    CREATE PROCEDURE insert_Equipment
    (
    	@equip_name VARCHAR(20),
    	@InsertFromEquipNo INTEGER,
    	@InsertTillEquipNo INTEGER
    )
    AS
    BEGIN
    	DECLARE @count INT; -- Used for the loop
    	SET @count = @InsertFromEquipNo;	
    
    	WHILE (@count <= @InsertTillEquipNo)
    	BEGIN
    		INSERT INTO Equipment (equip_no, equip_type) VALUES ('eq' + CAST(@count AS VARCHAR(4)), @equip_name);
    		SET @count = @count + 1;
    	END
    END;
    GO
    -- Execute the proc with parameters
    EXEC insert_Equipment @equip_name = 'Computer', @InsertFromEquipNo = 6053, @InsertTillEquipNo = 6177
    EXEC insert_Equipment @equip_name = 'Laptop', @InsertFromEquipNo = 6178, @InsertTillEquipNo = 6200
    -- Verify data
    SELECT * FROM Equipment_Details
    SELECT * FROM Equipment
    GO



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)


    • Edited by Aalamjeet Rangi Saturday, June 02, 2012 3:30 PM Inserted screenshot of results.
    Saturday, June 02, 2012 3:21 PM
  • Hi try this

    CREATE PROCEDURE insert_Equipment
    (
    	@equip_name VARCHAR(20),
    	@num INTEGER
    )
    AS
    BEGIN
    	SET @num = 6053;
    	SET @equip_name = 'Computer';
    	WHILE (@num <= 6177)
    	BEGIN
    		INSERT INTO Equipment VALUES ('eq'+Cast(@num As Varchar(5)), @equip_name);
    		SET @num = @num + 1;
    	END
    END


    PS.Shakeer Hussain

    • Marked as answer by favourGreen Monday, June 04, 2012 1:10 PM
    Saturday, June 02, 2012 3:40 PM
  • Hi, Shakeer

    I made a minor modifications to my code based on yours and managed to get the data inserted.

    Actually, there is also a missing "EXEC" statement which I didn't add previously, that's why I couldn't see any effect from the stored procedure.

    Thanks all for your advice.

    Monday, June 04, 2012 1:10 PM