SQL JOIN AND STORED
PROCEDURE GIVEN BELOW....
SELECT * FROM studentmark
SELECT * FROM studentdetails
SELECT * FROM course
select sd.sname,sd.sregno,sd.saddress,sd.sdob,sm.subject,sm.scode,sm.mark,c.course,c.patch,c.semester from studentdetails sd inner
join studentmark sm on
sd.sregno=sm.sregno inner join course c on c.sregno=sm.sregno
SELECT * FROM studentmark
select sd.sname,sd.sregno,c.course,AVG(sm.mark)from studentdetails sd inner
join studentmark sm on
sd.sregno=sm.sregno inner join course c on sm.sregno =c.sregno group by sd.sname ,sd.sregno ,c.course order by sd.sregno desc
select sd.sname,sd.sregno,c.course,AVG(sm.mark)from studentdetails sd inner
join studentmark sm on
sd.sregno=sm.sregno inner join course c on sm.sregno =c.sregno group by sd.sname ,sd.sregno ,c.course order by sd.sregno asc
SELECT * FROM studentmark
SELECT * FROM studentdetails
SELECT * FROM course
select sd.sdob,sd.sregno,sd.sname,sm.subject,sm.mark from studentdetails sd left
join studentmark sm on
sd.sregno=sm.sregno
select sd.sdob,sd.sregno,sd.sname,sm.subject,sm.mark from studentdetails sd left
outer join
studentmark sm on sd.sregno=sm.sregno
select sd.sdob,sd.sregno,sd.sname,sm.subject,sm.mark from studentdetails sd right
join studentmark sm on
sd.sregno=sm.sregno
select sd.sdob,sd.sregno,sd.sname,sm.subject,sm.mark from studentdetails sd full
join studentmark sm on
sd.sregno=sm.sregno
SELECT * FROM studentmark
SELECT sm.sregno,sd.sname,c.course,c.patch,sum(sm.mark) FROM
studentmark sm inner join
studentdetails sd on sm.sregno=sd.sregno inner join course c on c.sregno =sd.sregno group by sm.sregno,sd.sname,c.course,c.patch
SELECT sm.sregno,sd.sname,c.course,c.patch,sum(sm.mark) FROM
studentmark sm inner join
studentdetails sd on sm.sregno=sd.sregno inner join course c on c.sregno =sd.sregno group by sm.sregno,sd.sname,c.course,c.patch having (SUM (sm.mark)>150)
SELECT * FROM studentdetails
WHERE sname IN ('pmp','Prabu')
SELECT * FROM studentdetails
WHERE sname
BETWEEN 'pmp' AND 'Prabu'
SELECT COUNT(*) FROM
studentdetails
SELECT sname, sregno, FORMAT(GETDATE(),'YYYY-MM-DD') as PerDate
FROM
studentdetails
SELECT LEN(Sname) FROM studentdetails
SELECT sregno , ROUND(mark,0) FROM studentmark
SELECT sname, sregno, Now() FROM studentdetails
SELECT * FROM studentdetails
CREATE PROCEDURE SP_SRECORDSMARKS
(
@REGID VARCHAR (50),
@SID INT =0 OUT
)
AS
DECLARE @COUNT INT
BEGIN
SELECT @COUNT =COUNT(*) FROM studentmark where sregno =@REGID
if (@COUNT >0)
begin
set @SID=1;
PRINT @SID
RETURN @SID
END
ELSE
BEGIN SET @SID =1
PRINT @SID
RETURN @SID
END
END
EXEC
SP_SRECORDSMARKS 1
create proc sp_srecordsNEW
AS
SET NOCOUNT OFF
SELECT * FROM
studentdetails
EXEC
sp_srecordsNEW
--SET NOCOUND OD,OFF
------FOR displaying how many row affected details
create proc sp_srecordsOFF
AS
SET NOCOUNT OFF
SELECT * FROM
studentdetails
EXEC
sp_srecordsOFF
----------------------------------------------
create proc sp_srecordsvalue
@regno varchar(50)
AS
SET NOCOUNT OFF
SELECT * FROM studentdetails
where sregno =@regno
exec
sp_srecordsvalue @regno='102'
exec
sp_srecordsvalue '102'
------------------------------------
--OUT PUT-------
create proc sp_srecordoutput
@var varchar(50)OUTPUT
AS
SET @var =
(SELECT TOP(1)sname FROM studentdetails
)
declare
@outputresult varchar(50)
exec
sp_srecordoutput @outputresult output
select
@outputresult
---------------------------
------RETURN
VALUE
create proc sp_RETURNVALUE
AS
RETURN '150'
declare @rvalue int
exec @rvalue=sp_RETURNVALUE
select @rvalue
-------------PRINT
---------
print 'HELLO WORLD'
declare @rvalue int
exec @rvalue=sp_RETURNVALUE
print @rvalue
--------------WHILE
LOOP-----------
SELECT * FROM studentmark
--------------------WHILE-------------------------------
create proc sp_while
as
begin
declare @count int
set @count =1
while(@count <=5)
begin
print @count
set @count =@count +1
end
end
---------------------------------------------------------
create proc sp_while1
as
begin
declare @count int
set @count =1
while(@count <=10)
begin
print @count
set @count =@count +1
end
end
----------
alter proc sp_while1test
as
begin
declare @count int
set @count =5
while(@count <=10)
begin
print @count
set @count =@count +1
end
end
---------passparameteres-----
create proc sp_whileparam
@myparam int
as
begin
declare @count int
set @count =@myparam
while(@count <=10)
begin
print @count
set @count =@count +1
end
end
exec
sp_whileparam 9
---------------------------------------------------------
exec sp_while1
exec
sp_while1test
create proc sp_testwhilenew
as
begin
declare @Next int
Set @Next =0
while 1=1
Begin
Select @Next =(Select MIN(mark) from studentmark having MIN(mark)>@Next )
if @Next is not null
continue
Else
Break
End
End
exec
sp_testwhilenew
exec
sp_testwhile
Declare @Next int
Set @Next=0
While 1=1
Begin
Select @Next=Select(Min(IntValueKey)
From YourTable
Where Min(IntValueKey)>@Next)
If @Next is not null
Continue
Else
Break
End
Create Procedure sp_GrantExecute
@UserName varchar(20)='Public'
AS
Declare @next varchar(100)
Select @next=''
While 1=1
begin
Select @Next=(select min(name) from sysobjects where
xtype='P' and name>@next)
if @next is null
break
else
exec('Grant
Execute On '+@next+' to '+@Username)
continue
end
CREATE PROC SupplierStats
-- ALTER PROC
SupplierStats
AS
BEGIN
SET nocount ON
DECLARE @imax INT,
@i INT
DECLARE @Contact VARCHAR(100),
@Company VARCHAR(50)
-- The RowID
identity column will be used for loop control
-- MSSQL declare
table variable
DECLARE @CompanyInfo
TABLE(
RowID INT IDENTITY ( 1 , 1 ),
CompanyName VARCHAR(100),
ContactName VARCHAR(50)
)
INSERT
@CompanyInfo
SELECT CompanyName,
ContactName
FROM Suppliers
WHERE ContactName LIKE
'[a-k]%'
ORDER BY CompanyName
SET @imax = @@ROWCOUNT
SET @i = 1
WHILE (@i <= @imax)
BEGIN
SELECT
@Contact = ContactName,
@Company = CompanyName
FROM @CompanyInfo
WHERE RowID = @i
------------------------------------------------------
-- INSERT
PROCESSING HERE
------------------------------------------------------
PRINT CONVERT(varchar,@i)+' Contact: ' + @Contact + ' at ' + @Company
SET @i = @i + 1
END -- WHILE
END -- SPROC
GO
-- Stored
procedure execution - test stored procedure
EXEC
SupplierStats
GO
DECLARE
@CustomerID INT =
0
-- Iterate over
all customers
WHILE (1 = 1)
BEGIN
-- Get next
customerId
SELECT TOP 1 @CustomerID =
CustomerID
FROM Sales.Customer
WHERE
CustomerID > @CustomerId
ORDER BY CustomerID
-- Exit loop if no
more customers
IF @@ROWCOUNT = 0 BREAK;
-- call your sproc
EXEC dbo.YOURSPROC @CustomerId
END
alter proc sp_testwhileexp
as
declare @smark int=0
while(1=1)
begin
select top 1 @smark =mark from
studentmark where mark >@smark order by mark
if @@ROWCOUNT =0 break;
exec
sp_testwhileexp @smarkx
end
exec
sp_testwhileexp
SP FOR ALL PROCESS
----------------------------
CREATE proc sp_comm
@Mode varchar(10),
@ID int,
@Name varchar(20),
@Place varchar(20)
As
Begin
if @Mode='Add'
begin
insert into det_tbl values(@Id,@Name,@Place)
End
if @Mode='Update'
begin
update
det_tbl set d_Name=@Name,d_Place=@Place where d_id=@id
end
if @Mode='Delete'
begin
delete from det_tbl where
d_id=@Id
end
if @Mode='Select'
begin
select * from det_tbl
end
End
No comments:
Post a Comment