store procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

store procedure

Author

Topic

s.mirzaei1990
Starting Member

12 Posts

Posted - 2014-08-05 : 15:43:44

hi i wanna make a update and delete store procedure for 2 tables,
how can i make it?
please

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-05 : 15:45:47

create proc someproc1
(@var1 varchar(50))
as
update table1
set column1 = @var1
go

create proc someproc2
(@var1 int)
as
delete
from table1
where column2 = @var1
go

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

s.mirzaei1990
Starting Member

12 Posts

Posted - 2014-08-06 : 11:05:11

this store procedure is for adding for 2 tables.

ALTER PROCEDURE [dbo].[NewMember]
-- Add the parameters for the stored procedure here
@NationalCode char(10),
@Firstname nvarchar(100),
@Lastname nvarchar(200),
@Gender nvarchar(10),
@Fathername nvarchar(100),
@BirthYear numeric,
@BirthMonth smallint,
@BirthDay smallint,
@Resume nvarchar(max),
@MembershipYear decimal,
@MembershipMonth smallint,
@MembershipDay smallint,
@ExpireYear decimal,
@ExpireMonth smallint,
@ExpireDay smallint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRANSACTION

INSERT INTO People VALUES (@NationalCode,@Firstname,@Lastname,@Gender,@Fathername,@BirthYear,@BirthMonth,@BirthDay,@Resume)
INSERT INTO Members VALUES (@NationalCode,@MembershipYear,@MembershipMonth,@MembershipDay,@ExpireYear,@ExpireMonth,@ExpireDay)

COMMIT

--ROLLBACK
END

must i make this store procedure for update ?

create updateMembers
@NationalCode char(10),
@Firstname nvarchar(100),
@Lastname nvarchar(200),
@Gender nvarchar(10),
@Fathername nvarchar(100),
@BirthYear numeric,
@BirthMonth smallint,
@BirthDay smallint,

AS
BEGIN
UPDATE people
set
NationalCode=@NationalCode
Firstname=@Firstname
Lastname=@Lastname
Gender=@Gender
Fathername=@Fathername
BirthYear=@BirthYear
BirthMonth=@BirthMonth
BirthDay=@BirthDay

for second table (members), what to do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-06 : 12:30:43

It's up to you. You can either put the code into one stored procedure or use 2.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources