CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Bulvak
Bulvak

Reputation: 1804

need to create a stored procedure but having issues

I am not sure why the below code is giving errors when I try to create a stored procedure. I am relatively inexperienced with SP.

CREATE PROCEDURE [dbo].[InsertDuplicateFields] 
    (@UniqueColID varchar(50), @IndividualID varchar(50) = null)
AS
Begin           
    INSERT INTO TableCollisionBegin 
        SELECT * FROM TableCollisionBegin 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableCollisionDetails 
        SELECT * FROM TableCollisionDetails 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableCollisionLocation 
        SELECT * FROM TableCollisionLocation 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableDriver 
        SELECT * FROM TableDriver 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableFollowUp 
        SELECT * FROM TableFollowUp 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableOfficerLogs 
        SELECT * FROM TableOfficerLogs 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TablePolice 
        SELECT * FROM TablePolice 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableRecVerified 
        SELECT * FROM TableRecVerified 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableSignature 
        SELECT * FROM TableSignature 
        WHERE [IndividualID] = @IndividualID;

    INSERT INTO TableTrailer 
        SELECT * FROM TableTrailer 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableValidateLog 
        SELECT * FROM TableValidateLog 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableVehicle 
        SELECT * FROM TableVehicle 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableWitness 
        SELECT * FROM TableWitness 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableInvolvedPerson 
        SELECT * FROM TableInvolvedPerson 
        WHERE [IndividualID] = @IndividualID;

    INSERT INTO TableStatement 
        SELECT * FROM TableStatement 
        WHERE [UniqueColID] = @UniqueColID; 

    INSERT INTO TableCollisionDiagram 
        SELECT * FROM TableCollisionDiagram 
        WHERE [UniqueColID] = @UniqueColID;
END

The error that I get is:

Line 21 Invalid column name 'IndividualID'.

If I remove the line of code inserting individualID then it gives the same error about the UniqueColID

This is strange because in that given table there IS a column IndividualID so I don't know why it won't recognize it....I typed this code in MS Excel then copy pasted it into notepad, could that be why the code isn't working?

I copied one row from that table and as you can see it DOES have IndividualID:

IndividualID
059D1263-F0F3-4D19-8C56-0FC7D2B5266E

Upvotes: 1

Views: 129

Answers (4)

Katie Kilian
Katie Kilian

Reputation: 6983

Double check your table TableInvolvedPerson and make sure the column IndividualID is spelled the same.

Updated

When you updated your question, you added a value for IndividualID. The value you added is a Guid, which can possibly be stored in SQL Server as a UNIQUEIDENTIFIER column. But the type you declared on your stored procedure is a VARCHAR. Can you double check your types? What is the type for IndividualID on both the original table and the table you are copying into? Do they match?

Upvotes: 1

DaveE
DaveE

Reputation: 3647

Double check you don't have an oddball Unicode character somewhere that looks like what you're expecting but isn't.

I'd also add error checking after each statement so you can get a sensible error message if one of them fails; you can then also rollback a transaction if this needs to be an all-or-nothing update.

Upvotes: 1

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Keep in mind that a query like this

INSERT INTO `a` SELECT * FROM `a` WHERE `c`=null

Won't insert you anything no matter what the values are. If the data in c is null, you must use IS NULL and never =null.

Upvotes: 0

Farhan
Farhan

Reputation: 2575

Why are you using single quotes around the parameters? Both those parameters are varchar and you don't need to do that.

Upvotes: 3

Related Questions