0

I want to Get the REcord from the user in one page of three table i.e Sender Receiver and Parcel and i write the store procedure for it as but when i save it. It gives the following ERROR I Can't understand the ERROR i.e Incorrect syntax at @P_ID

The Store Procedure code is as

ALTER PROCEDURE dbo.ExSenderReceiveParcel

@S_Name varchar(Max),
@S_Country varchar(Max), 
@S_City varchar(Max), 
@S_StreetNo varchar(Max), 
@S_HouseNo varchar(Max), 
@S_Phone varchar(Max), 
@S_Mobile varchar(Max), 
@S_Email varchar(Max), 
@S_CreditCardNo varchar(Max), 
@S_PinCode varchar(Max),

@R_Name varchar(Max), 
@R_Country varchar(Max), 
@R_City varchar(Max), 
@R_StreetNo varchar(Max), 
@R_HouseNo varchar(Max), 
@R_Phone varchar(Max), 
@R_Mobile varchar(Max), 
@R_Email varchar(Max),

@P_Weight varchar(MAX),
@P_Status varchar(MAX),
@P_Location varchar(MAX),
@P_Id numeric(18, 0) out

AS Begin

DECLARE @S_Id numeric(18, 0),
@R_Id numeric(18, 0)

INSERT INTO Sender
                      (Name, S_Country, S_City, StreetNo, HouseNo, Phone, Mobile, Email, CreditCardNo, PinCode)
VALUES     (
    @S_Name,
    @S_Country,
    @S_City, 
    @S_StreetNo, 
    @S_HouseNo, 
    @S_Phone, 
    @S_Mobile, 
    @S_Email, 
    @S_CreditCardNo, 
    @S_PinCode);


    SET @S_Id = SCOPE_IDENTITY();

    INSERT INTO Receiver
       (Name, R_Country, R_City, StreetNo, HouseNo, Phone, Mobile, Email)
VALUES 
        (
            @R_Name, 
            @R_Country, 
            @R_City, 
            @R_StreetNo, 
            @R_HouseNo, 
            @R_Phone, 
            @R_Mobile, 
            @R_Email
        );


        SET @R_Id = SCOPE_IDENTITY();

        INSERT INTO Parcel
    (Weight, Status, Location, Sender_Id, Receiver_Id)
VALUES     
(
    @P_Weight,
    @P_Status,
    @P_Location,
    @S_Id,
    @R_Id
);

SET @P_Id = SCOPE_IDENTITY();

Select @P_Id
Muhammad Nauman
  • 213
  • 1
  • 4
  • 20

1 Answers1

0

You don't have an END clause at the end of your procedure.

It should be 

CREATE PROC pSomething (@params) AS     
BEGIN..<code here>..END

Also, your types should be cleaned up; if everything in your database is a VARCHAR(MAX) then you will run into some space issues at some point. SCOPE_IDENTITY also returns an integer, not a numeric value; it may not be a big deal in this context, but it's a pet peeve of mine :)

Stuart Ainsworth
  • 12,792
  • 41
  • 46