Stored Procedure For Insert

Posted by Rrana under Sql Server on 8/25/2011 | Points: 10 | Views : 14889 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

I am using Sqlserver 2008

I am writing Stored Procedure for insert and update and i am using input and out put parameters, i want to auto increment my primary key in stored procedure. i want the sample code.

can anyone please help me

Thanks in advance

Sucharitha Goud
Bank Of America..

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: PandianS on: 8/25/2011 [Member] [MVP] Silver | Points: 25

0	Hi Pls walk through the following steps... /Creating one Sample Table/ If OBJECT_ID('Tb_SQL') Is Null Create Table Tb_SQL ( Id Int Identity(1,1) Primary Key, Name Varchar(50), Place Varchar(50) ) Go /Sample Stored Procedure to Insert and Return the Autogenerated ID/ If OBJECT_ID('Usp_InsertProcedure') Is Not null Drop Proc Usp_InsertProcedure Go Create Proc Usp_InsertProcedure ( @Name Varchar(50), @Place Varchar(50), @ID Int Output )As Begin Set Nocount On Insert Tb_SQL(Name, Place) Values(@Name, @Place) Select @ID = @@IDENTITY End Go /Executing the Stored Procedure/ Declare @AutoID Int EXEC Usp_InsertProcedure @Name='Sucharitha Goud', @Place='Chennai', @ID = @AutoID Output Select @AutoID [Auto Generated ID] Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Pls walk through the following steps...

/*Creating one Sample Table*/

If OBJECT_ID('Tb_SQL') Is Null

Create Table Tb_SQL

(

Id  Int Identity(1,1) Primary Key,

Name Varchar(50),

Place Varchar(50)

)

Go

/*Sample Stored Procedure to Insert and Return the Autogenerated ID*/

If OBJECT_ID('Usp_InsertProcedure') Is Not null

Drop Proc Usp_InsertProcedure

Go

Create Proc Usp_InsertProcedure

(

@Name	Varchar(50),

@Place	Varchar(50),

@ID	Int Output

)As

Begin

	Set Nocount On

	Insert Tb_SQL(Name, Place) Values(@Name, @Place)

	Select @ID = @@IDENTITY 

End

Go

/*Executing the Stored Procedure*/

Declare @AutoID	Int

EXEC Usp_InsertProcedure @Name='Sucharitha Goud', @Place='Chennai', @ID = @AutoID Output

Select @AutoID [Auto Generated ID]

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Kunalverma31 on: 8/26/2011 [Member] Starter | Points: 25

0	Best way to do it set IDENTITY=true for primary key column and identity increment to 1. Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriramnandha on: 9/30/2011 [Member] Starter | Points: 25

0	create table employee(empid ind identity(1,1),empname varchar(80),empcity varchar(90)) insert into employee values('sriram','chennai') begin create procedure sp_employee sp_empname varchar(90), sp_empcity varchar(90) as insert into employee (empname,empcity)values(@sp_empname,sp_empcity) if @@error<>0 print "fatal errror" end sriram Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

create table employee(empid ind identity(1,1),empname varchar(80),empcity varchar(90))

insert into employee values('sriram','chennai')
begin
create procedure sp_employee
sp_empname varchar(90),
sp_empcity varchar(90)
as
insert into employee (empname,empcity)values(@sp_empname,sp_empcity)
if @@error<>0
print "fatal errror"
end

sriram

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

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

0	CREATE PROCEDURE insertTLtask ( @user_id int, @project_id int, @task_type_id int, @frequency smallint, @crdate datetime ) AS BEGIN insert into TL_task(user_id,Project_id,task_type_id,frequency,created_at)values(@user_id,@project_id,@task_type_id,@frequency,@crdate) 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

CREATE PROCEDURE insertTLtask
(
@user_id int,
@project_id int,
@task_type_id int,
@frequency smallint,
@crdate datetime
)
AS
BEGIN
insert into TL_task(user_id,Project_id,task_type_id,frequency,created_at)values(@user_id,@project_id,@task_type_id,@frequency,@crdate)
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: Venkim.09 on: 10/9/2012 [Member] Starter | Points: 25

0	Hello,I am writing Stored Procedure for insert and update and i am using input and out put parameters, i have used auto increment my primary key in stored procedure. that is the code in given attachment. Thanks & regards, Venky Mokka Download source file Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Venkim.09 on: 10/9/2012 [Member] Starter | Points: 25

0	Hello,I am writing Stored Procedure for insert and update and i am using input and out put parameters, i have used auto increment my primary key in stored procedure. that is the code in given attachment. Thanks & regards, Venky Mokka BSD Structural Engineering Pvt,Ltd Download source file Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts