Insert command through Stored Procedure

Posted by Rrana under Sql Server on 9/1/2011 | Points: 10 | Views : 3532 | Status : [Member] | Replies : 6
Hi,

can any body please give the code of S.P

Table Structure is;

TRAINING_ID int identity(1,1)Unchecked------primary key
TRAINER_NAME varchar(50) Checked
SUBJECT varchar(100) Unchecked
START_DATE_TIME datetime Unchecked
END_DATE_TIME datetime Unchecked
LOCATION varchar(100) Checked
CREATE_DATE datetime Unchecked
MODIFIED_DATE datetime Checked
ISACTIVE bit Unchecked


i want primary key as output parameter and remaining all are input.

i want Stored procedure for this and also C#.net code for S.P

Thanks in advance

Sucharitha Goud
Bank Of America..



Responses

Posted by: Seenuvasan on: 9/1/2011 [Member] Starter | Points: 25

Up
0
Down
Try like this. but i didn't check below code.
pls replace <TABLE_NAME> to ur table name.

CREATE PROCEDURE InsertRec 

(
@TRAINER_NAME varchar(50),
@SUBJECT varchar(100) ,
@START_DATE_TIME datetime ,
@END_DATE_TIME datetime ,
@LOCATION varchar(100) ,
@CREATE_DATE datetime ,
@MODIFIED_DATE datetime ,
@ISACTIVE bit ,

@ID int OUTPUT
)
AS
BEGIN

INSERT INTO <TABLE NAME>(@TRAINER_NAME,@SUBJECT,@START_DATE_TIME,@END_DATE_TIME,
@LOCATION,@CREATE_DATE,@MODIFIED_DATE
)

SELECT @ID=max(TRAINING_ID) from <TABLE_NAME>

END
GO


Thanks,
Seen

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rickeybglr on: 9/5/2011 [Member] Starter | Points: 25

Up
0
Down
@ Sucharitha , u want to insert data using 3 tier or 1 tier ???

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 11/24/2011 [Member] Starter | Points: 25

Up
0
Down

create procedure sp_ProductDetaile
@product_id int,
@Productname varchar(90)
as
Begin
insert into Product_details (productid,product_Name)
values
(@product_id,@Productname)
end
Go

sriram

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 11/29/2011 [Member] Starter | Points: 25

Up
0
Down
CREATE PROCEDURE insertTLtask
(
@TRAINER_NAME varchar(50),

@SUBJECT varchar(100) ,

@START_DATE_TIME datetime ,

@END_DATE_TIME datetime ,

@LOCATION varchar(100) ,

@CREATE_DATE datetime ,

@MODIFIED_DATE datetime ,

@ISACTIVE bit ,
@ID int OUTPUT

)
AS
BEGIN
INSERT INTO <TABLE NAME>(@TRAINER_NAME,@SUBJECT,@START_DATE_TIME,@END_DATE_TIME,@LOCATION,@CREATE_DATE,@MODIFIED_DATE) values(Column_name ,Column_name,Column_name,Column_name,Column_name,Column_name,)



END

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 11/29/2011 [Member] Starter | Points: 25

Up
0
Down
INSERT INTO <TABLE NAME>(@TRAINER_NAME,@SUBJECT,@START_DATE_TIME,@END_DATE_TIME,@LOCATION,@CREATE_DATE,@MODIFIED_DATE) values(TRAINER_NAME,SUBJECT,START_DATE_TIME,END_DATE_TIME,LOCATION,CREATE_DATE,MODIFIED_DATE )

replace command in S>P

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vanajasethu on: 11/29/2011 [Member] Starter | Points: 25

Up
0
Down
CREATE PROCEDURE <procname>
(
@TRAINER_NAME varchar(50),
@SUBJECT varchar(100) ,
@START_DATE_TIME datetime ,
@END_DATE_TIME datetime ,
@LOCATION varchar(100) ,
@CREATE_DATE datetime ,
@MODIFIED_DATE datetime ,
@ISACTIVE bit,
@TRAINING_ID int OUTPUT
)
AS
BEGIN

INSERT INTO <TABLE NAME>(TRAINER_NAME,SUBJECT,START_DATE_TIME,END_DATE_TIME,LOCATION,CREATE_DATE,MODIFIED_DATE,ISACTIVE)
values(@TRAINER_NAME,@SUBJECT,@START_DATE_TIME,@END_DATE_TIME,
@LOCATION,@CREATE_DATE,@MODIFIED_DATE,@ISACTIVE)


SELECT @TRAINING_ID=max(TRAINING_ID) from <TABLE_NAME>

SELECT @TRAINING_ID 'TRAINING_ID'

END



SqlConnection conn = new SqlConnection(constring);
conn.Open();
SqlCommand cmd = null;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("TRAINER_NAME", SqlDbType.VarChar).Value = sTrainerName;
cmd.Parameters.Add("SUBJECT", SqlDbType.VarChar).Value = sSubject;
cmd.Parameters.Add("START_DATE_TIME", SqlDbType.Datetime).Value = startDate;
cmd.Parameters.Add("END_DATE_TIME", SqlDbType.Datetime).Value = endDate;
cmd.Parameters.Add("LOCATION", SqlDbType.VarChar).Value = sLocation;
cmd.Parameters.Add("CREATE_DATE", SqlDbType.Datetime).Value = createDate;
cmd.Parameters.Add("MODIFIED_DATE", SqlDbType.Datetime).Value = modifyDate;
cmd.Parameters.Add("ISACTIVE",SqlDbType.Bit).Value = 0;
cmd.Parameters["TRAINING_ID"].Direction = ParameterDirection.Output;
TRAINING_ID = cmd.Parameters["TRAINING_ID"].Value;
cmd.ExecuteNonQuery();
conn.Close();

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response