CODEWITHSUNDEEP
sqlsql-serverstored-procedures
March
March

Reputation: 635

Stored Procedure, can you help me?

Below is my stored procedure. I want use stored procedure select all row of date from tbl_member and insert 2 table. But it's not work. Some one can help me?

Create PROCEDURE sp_test
AS
BEGIN
    SET NOCOUNT ON;

    Declare @A Varchar(255), @B Varchar(255), @C Varchar(255), @D int

    Declare Table_Cursor Cursor 
    For select A, B, C from tbl_Member Open Table_Cursor 
        Fetch Next From Table_Cursor 
        Into @A, @B, @C While(@@Fetch_Status=0)

    Begin Exec(
        'insert into NewMember (A, B, C, D) values (@A, @B, @C, @D)
        set @D = @@IDENTITY
        Insert into MemberId (Mid) VALUES(@D)   
    )
    Fetch Next From Table_Cursor Into @A, @B, @C End Close Table_Cursor
    Deallocate Table_Cursor
END
GO

Upvotes: 0

Views: 208

Answers (2)

HLGEM
HLGEM

Reputation: 96542

Please pay particular attention to what Pete said about @@identity. The reason it is bad to ever use @@identity is that if a trigger is ever added to the table that inserts to another table with an identity, that is the identity that is returned not the one you just inserted. This can lead to massive data integrity problems that may not be caufght for months.

Even if you kept the cursor (which I do not recommend, cursors are a very bad way to do inserts as they are very slow compared to the set-based solution that Pete gave), your code could not do the insert to the first table if column D is the identity column (which I would guess it is since you later try to assign D to the identity value). That is because you are trying to put a value into a column that cannot accept a value as it is autogenerated. If D is the identity then do not use it inthe insert at all, just define the columns you need to insert into. This would be true of Pete's solution as well, if D is in fact your identity column.

Upvotes: 3

Pete OHanlon
Pete OHanlon

Reputation: 9146

The first thing I can see here is that you are using a cursor when you don't need to. You can rewrite the first query as:

INSERT INTO NewMember(A, B, C, D)
SELECT A, B, C, D
FROM tbl_member

Then, I would have an INSERT trigger against NewMember that inserted the identity column.

create trigger myInsertTrigger
on newmember
for insert
as
insert into memberid(mid)
select <<identity_column>> from inserted

BTW - it's a bad idea to use @@IDENTITY to get the identity of an insert. Use the SCOPE_IDENTITY function instead.

Upvotes: 8

Related Questions