uses of user defined functions and transaction in sql server

create database mithilesh

create table mth(mid int,ename varchar(50),ecity varchar(50),emob varchar(max))

insert into emp values(1,'mithiles','varansi','546546')

select * from emp

/* craete user defined function*/

create function fn(@a int)

returns varchar(50)

as begin

return (select ename from emp where eid =@a)

end

/*for user defined function call*/

select master.dbo.fn(1)

output:-

mithiles

/* inline tables udf return a table that was create dby a single select statement.*/

/* create inline table user defined function*/

/*do not use begin and end on creation of inline tables udf*/

/* its a function which returns a whole tables named "mth".*/

create function func()

returns table

as

return (select * from mth)

/* calling of inline tables udfs*/

select * from master.dbo.func()

output:-

1 a b c

2 d e f

3 g h i

/* transaction sql server*/

use master

select * from emp

select * from mth

alter proc pr

as

begin tran

declare @i int

update emp set ecity='ghazipur' where eid=1

select @i=@@error

if(@i<>0) goto problem

update mth set ename='varun' where mid=2

select @i=@@error

if(@i<>0) goto problem

commit tran

problem:

if(@i<>0)begin

print 'error'

rollback tran

return 1

end

exec pr