Store Procedure

Hi ,

am using inline insert query in my code.

  cmd.CommandText = "INSERT Person (EmployeeNo,NamePrefix,FirstName, MiddleName, LastName, NameSuffix,Contractor,HighestEducationalLevel, BirthDate, Gender ) VALUES("
                                                   + item.EmployeeNumber + ","
                                                   + "'" + item.Title + "'" + ","
                                                   + "'" + item.PreferredName + "'" + ","
                    //+ "'" + item.MiddleName + "'" + ","
                                                   + "'" + item.MiddleName.Replace("'", "''") + "'" + ","
                                                    + "'" + item.Surname.Replace("'", "''") + "'" + ","
                                                    + "'" + item.Suffix + "'" + ","
                                                    + "'" + item.PayPoint + "'" + ","
                                                    + "'" + item.HighestEducationLevel + "'" + ","
                                                    + "'" + item.BirthDate + "'" + ","
                                                    + "'" + item.Sex + "'" + ")";

                cmd1.CommandText = "INSERT HOMEADDRESS (EmployeeNo,AddressLine1,AddressLine2,AddressLine3,City,State,PostCode ) VALUES("
                                                   + item.EmployeeNumber + ","
                                                    + "'" + item.ResidentialAddress1+ "'" + ","
                                                    + "'" + item.ResidentialAddress2 + "'" + ","
                                                    + "'" + item.ResidentialAddress3 + "'" + ","
                                                    + "'" + item.ResidentialAddress2 + "'" + ","
                                                    + "'" + item.ResidentialAddress3 + "'" + ","
                                                    + "'" + item.ResidentialPostCode + "'" + ")";

Open in new window


Any one give me an idea how can i combine both and write it in one Store procedure in that case i can clean up some mess in my code.

Thanks in Advance!
Sha1395Asked:
Who is Participating?
 
lenordisteConnect With a Mentor Commented:
in short you could write this stored procedure:
CREATE PROCEDURE InsertPersonalInformation
    @EmployeeNo varchar(50), 
    @NamePrefix varchar(50) ,
    @StateCode varchar(50) 
AS 

    SET NOCOUNT ON;
  INSERT Person (@EmployeeNo,@NamePrefix)
  INSERT HOMEADDRESS (@EmployeeNo,@StateCode)
GO

Open in new window


you would then call your stored procedure like so:
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EmployeeNo", item.EmployeeNumber));
cmd.Parameters.Add(new SqlParameter("@NamePrefix", item.NamePrefix));
cmd.Parameters.Add(new SqlParameter("@StateCode", item.StateCode));
cmd.ExecuteNonQuery();

Open in new window

0
 
BurniePCommented:
You can use semicolumn to separate the 2 calls.

...
+ "'" + item.Sex + "'" + ");"
+ "INSERT HOMEADDRESS (EmployeeNo,AddressLine1,AddressLine2,AddressLine3,City,State,PostCode ) VALUES("
+ ...

0
 
Sha1395Author Commented:
Thanks for your comment BurnieP.The reason i want to do in SP is

This my actual Insert command

               jobCmd.CommandText = "INSERT HOMEADDRESS (EmployeeNo,StateCode) VALUES("
                                                     + item.EmployeeNumber + ","
                                                      + "'" + item.CityLocation+ "'" + ")";


Am pulling value for City Location to State Code.

For Example

if the city location is Detroit then am passing the value "MI" to State code in DB.

How can i achieve,i believe its easy thru SP.
0
 
lenordisteCommented:
oh and don't forget to specify the stored procedures name in the constructor of SqlCommand:
SqlCommand cmd  = new SqlCommand(      "InsertPersonalInformation", conn);
0
 
Sha1395Author Commented:
Hi lenordiste,

Thanks for your SP can you help to replace the string inside SP.

For example

am getting city location ="Detroit"

then for State code ="MI" ( i have to insert this value to my State Code Column)

how can i write "Case"Statetment or if condition inside SP ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.