SQL training Procedure and views

simple view


create view Emp_view
as

select empid,ename,gender from emp

insert into Emp_view values (11,'sssss','M')


Alter view Emp_view as select empid,ename,gender,sal,deptno from emp





multitable view

create view emp_dept
as
select empid,ename,emp.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno

insert into emp_dept values (111,'sdff',1,'marketing')


giving error not allowed to insert records on multiple jointable view




temporory table example procedure

Create Procedure tempororytable
@startWith nvarchar(50)
As
Begin
Declare @EMPCOUNT int
Set nocount on;
Select p.empid, p.ename
into #Temp
 from emp as p
where
p.ename like @startWith + N'%';
Select
t.ename,  COUNT(*) as EMPCOUNT from #Temp as t
group by
t.ename;

--Print EMPCOUNT
Drop table #Temp;
End
GO



Cursor Example

Create procedure CursorDemo
as
Declare @Ename varchar (20)
Declare @Ename1 Varchar(30)

Declare @getEname CURSOR

Set @getename  = CURSOR For

select ename from emp

Open @getEname

Fetch Next from @getEname into @Ename
while @@FETCH_STATUS  = 0
Begin


set @Ename1 = (@Ename + 'AAAA')
Print @Ename1
Fetch Next
From @getEname into @Ename
END
Close @getEname
Deallocate @getEname
GO

EXEC CursorDemo






Procedure @@ERROR and @@rowcount

Create Procedure GetData
(
@DEPTNo int,
@sal int
)
As
begin
Declare @ErrorVar int;
Declare @RowcountVar int;

update emp set sal= sal + 1000 where deptno = @DEPTNo

select @ErrorVar = @@ERROR,
@RowcountVar = @@ROWCOUNT;

if(@ErrorVar <> 0)
Begin
    if @ErrorVar = 547
  BEGIN
     print N'Invalid';
     return 1;
  End
Else
   Begin
   Print N'Error';
   return 2;
   End

End
Else
Begin
if @RowcountVar = 0
Begin
 print 'warning';
End
Else
Begin
print 'updated'
Return 0;
End
end
End



EXEC GetData 15,1000