stored procedure

create table stu ( sname varchar(20),sid int,sdept varchar(20),scountry nvarchar(20));

insert into stu values('chakri',93,'cse','ind');


insert into stu values('roy',15,'it','eng');


insert into stu values('root',9,'it','eng');


insert into stu values('dhoni',7,'cse','ind');


insert into stu values('sachin',10,'it','ind');


insert into stu values('ponting',220,'eee','aus');


select * from emp;

create table emp( ename varchar(20),eid int,ejob varchar(20),ecountry nvarchar(20));

insert into emp values('ponting',11,'batsmen','aus');


insert into emp values('dhoni',7,'batsmen','ind');


insert into emp values('lee',1,'bowler','aus');

insert into emp values('parthiv',90,'keeper','ind');



// creating a stored procedure


create  procedure bwthyd
as

begin

select * from emp;


select * from stu;

end

bwthyd


select * from emp;



insert into stu values('ponting',220,'eee','aus');

insert into stu values('ponting',220,'eee','aus');


insert into stu values('ponting',220,'eee','aus');


CREATE PROCEDURE [dbo].[ShowFaq] AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM stu;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM stu;
END;

[dbo].[ShowFaq]

create procedure p
as

begin

      return null;

 end
p

create proc pp

as

begin

  print  ' blue water technologies'

end

go

pp




create proc i

as

print 'hi'

i

select * from emp;


create proc e
(        
     @ejob nvarchar(25)




)


as

begin


select * from emp where ejob=@ejob ;

end
e  'bowler'



--CREATE PROCEDURE
CREATE PROCEDURE ELIGIBILITY
(
  @MaxAge INT --PARAMETER
)
AS
BEGIN
      IF(@MaxAge>=18) --CONDITION
            BEGIN
                  PRINT 'YOU ARE ELIGIBLE FOR ADMITION'--CONDITION TRUE
            END
      ELSE
            BEGIN
                  PRINT 'SORRY, YOU ARE NOT ELIGIBLE FOR ADMITION'--CONDITION FALSE
            END
END
Execute Procedure with parameter and see output

-- EXECUTE PROCEDURE
EXEC ELIGIBILITY 2













create proc z1


as
begin

DECLARE @site_name VARCHAR(40);
SET @site_name = 'SQA';
IF @site_name = 'DBA'
   CREATE TABLE dba_links (url VARCHAR(256));
ELSE IF @site_name = 'SQA'
   CREATE TABLE sqa_link(url VARCHAR(256));
ELSE
   PRINT 'Unknown site name: '+@site_name;
end


exec z1

select * from sqa_link






create proc cas


as
begin

DECLARE @Name varchar(50)
SET @Name = 'hyd'
SELECT
Case  @Name
 WHEN  'bang'  THEN  'Name Deepak'
WHEN  'pune' THEN  'Name Found Manoj'
WHEN  'hyd' THEN   'Name Found Rohatash'
ELSE 'Name not Found'


end

create table cha(sname varchar(20)

select * from sys.messages





 BEGIN TRY
DECLARE @num INT, @msg varchar(200)
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
PRINT 'Error occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
END CATCH
GO