Cursor
in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.
Declare ex_cursor Cursor
in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.
Declare ex_cursor Cursor
for select userid,username
from employee
declare @userid int
declare @username varchar(50)
open ex_cursor
fetch next from ex_cursor into @userid,@username
while @@FETCH_STATUS=0
begin
print (cast(@userid as varchar(5)) + '-' + @username)
fetch next from ex_cursor into @userid,@username
end
close ex_cursor
deallocate ex_cursor
STORED PROCEDURE
a) Stored procedure allows modular programming.
b) b) Stored Procedure allows faster execution.
c) c) Stored Procedure can reduce network traffic.
STORED PROCEDURE
a) Stored procedure allows modular programming.
b) b) Stored Procedure allows faster execution.
c) c) Stored Procedure can reduce network traffic.
d) d) Stored procedures provide better security to your data.
create procedure sp_employee
AS
BEGIN
select * from Employee where
UserId=1
END
exec
sp_Employee
drop procedure sp_employee
FUNCTION
used to reduce redundancy of query or avoid joins.
used to reduce redundancy of query or avoid joins.
create function getusername
(@userid int)
returns varchar(50)
as
begin
declare @name varchar(50)
select @name=username from
Employee where UserId=@userid
return @name
end
select * from Employee where UserName=dbo.getusername(1)
