SQL Stored Procedures

Stored procedures:
------------------------

INSERT
------------------
insert into dept values(13,'krishna',40000)
-------------
create proc sp_insert(@dno int,@dnm varchar(50),@dsal int)
as
insert into dept values(@dno,@dnm,@dsal)

exec sp_insert 13,'krishna',40000

DELETE
--------------------
delete from dept where dno=10
----------
create proc sp_delete(@dno int)
as
delete from dept where dno=@dno
exec sp_delete 10    

UPDATE
-------------
update dept set dname='xxx' dsal='1100' where dno=11
-------
create proc sp_update(@dno int,@dnm varchar(50),@ds int)
as
update dept set dname=@dnm,dsal=@ds where dno=@dno
exec sp_update 11,'venu',11000        


SELECT RETURN
-------------------
select * from dept
-------
create proc sp_selectreturn
as
select * from dept
return
exec sp_selectreturn  

SELECT
---------
create procedure sp_select(@dno int)
as
select dname,dsal from dept where dno=@dno
exec sp_select 10  

------------------------

ALTER COLUMN IN A TABLE

-->create table Users(UserId varchar(50),UserName varchar(50),UserRole varchar(50),ActiveUser int,permission varchar(50))

-->EXEC sp_rename 'Users.UserName', 'Password', 'column'

Syntax:EXEC sp_rename 'tablename.oldcolumn_name' 'newColumn_name'  'column'

-----------------------------------------
ADD NEW COLUMN IN A EXISTING TABLE


Ex:
alter table users add UserName varchar(50)


systax: 
Alter table table_name ADD new_columnName type(size)