Transaction in sql server

What is transaction?

A transaction is a group of commands that change the data store in a database. a transaction, is treated as a single unit. a transaction is ensures that, either all of the commands succeed,or none of them. If one of the commands in the transaction fails, all of the commands fails, and any data that was modified in database is rolled back. In this way, transaction maintain the integrity of data in a database.

Transaction processing follows these steps : 

1.Begin a transaction.

2.Process database commands.

3.Check for errors.

If errors occurred,

  rollback the transaction,

Else

  commit the transaction

Note : we are not able to see the un-committed changes by using transaction in the query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

By setting isolation level read uncommitted in query the we can see the changes value in database.

We have apply the transaction on the following tables.

create table tblCustomerAddress(

id int identity primary key,

customer_code nvarchar(100),

city nvarchar(10),

pin_code nvarchar(6)

)

create table tblMailingAddress(

id int identity primary key,

customer_code nvarchar(100),

city nvarchar(10),

pin_code nvarchar(6)

)

create procedure SP_UpdateAddress

as

begin

begin try

begin transaction

update tblCustomerAddress set city='Delhi'

where id=1 and customer_code='101'

update tblMailingAddress set city='Delhi'

where id=1 and customer_code='101'

commit transaction

end try

begin catch

rollback transaction

end catch

End

The above procedure is working fine when execute the store procedure.

alter procedure SP_UpdateAddress

as

begin

begin try

begin transaction

update tblCustomerAddress set city='Delhi'

where id=1 and customer_code='101'

update tblMailingAddress set city='moradabad moradabad'

where id=1 and customer_code='101'

commit transaction

end try

begin catch

rollback transaction

end catch

End

The above procedure is not working fine because of that we are trying to update the longer value in city column which are set only 10 character limit in database,  so one query is fail then other query also fail here.

Difference between function and stored procedure

The Function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.

 Function can have only input parameters for it whereas procedure can have input or output parameters.

Function can be called from Procedure whereas Procedure cannot be called from a function.

How to create stored procedure for insert data into table in sql server?

create procedure sp_InsertUserType(
@type_Name nvarchar(200),
@isActive bit )
as 
begin
INSERT INTO tblUserType(type_Name,isActive) VALUES(@type_Name,@isActive)
end
go


exec sp_InsertUserType 'Admin','true'

What is stored procedure in sql server

A stored procedure in SQL Server is a group of one or more Transact-SQL statements.

Stored procedure syntax:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute Stored procedure :

EXEC procedure_name