All about sql

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.

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.

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)