Stored Procedure For Insert

Posted by Rrana under Sql Server on 8/25/2011 | Points: 10 | Views : 11870 | Status : [Member] | Replies : 6
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..



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response