Wednesday, 21 December 2011

How to write basic stored procedures (update, save, delete)


Delete row from given id, here commentId is id which is provided by user and TUSER_COMMENT is table name , USER_COMMENT_ID is id column name and proc_DELETE_COMMENT is procedure name

Delete

CREATE PROCEDURE proc_DELETE_COMMENT (@commentId int)

AS
BEGIN
delete from TUSER_COMMENT where USER_COMMENT_ID=@commentId
END

Insert Row
CREATE PROCEDURE proc_HitList_Registration(@Name varchar(25),@Email varchar(25),@Pwd varchar(15),@Gender varchar(6),@DOB datetime,@Role varchar(50))

AS
BEGIN
Insert into TUSER_REGISTRATION values(@Name,@Email,@Pwd,@Gender,@DOB,@Role);
END

Update Row
CREATE PROCEDURE proc_HitList_UpdateProfile

(@Name varchar(25),@Email varchar(25),@Pwd varchar(15),@Gender varchar(6),@DOB datetime,@Role varchar(50))
AS
BEGIN
Update TUSER_REGISTRATION Set User_Registration_Name=@Name,User_Registration_Pwd=@Pwd,User_Registration_Gender=@Gender,User_Registration_DOB=@DOB,User_Registration_Role=@role where User_Registration_Email=@Email
END

Select Row
CREATE PROCEDURE proc_HITLIST_ADMIN_PASSWORD (@UserMailId varchar(25))

AS
BEGIN
select USER_REGISTRATION_PWD FROM TUSER_REGISTRATION WHERE USER_REGISTRATION_EMAIL=@UserMailId
END








 

No comments:

Post a Comment