Search content within the blog

Friday, June 3, 2011

SQL Concepts with examples

VIEWS
--------
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No


INDEX
------
CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)


CURSORS
---------
Declare @name varchar(32)
declare cursorname cursor read_only for
select name from Ashwin

open cursorname
fetch NEXT from cursorname into @name
while(@@Fetch_status=0)
begin
print @name
fetch next from cursorname into @name
end
close cursorname
deallocate cursorname

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

Triggers
---------
Create TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserrted.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color+'altered', Material+'altered'
FROM inserted
END

CREATE TRIGGER [trglogin]
ON [dbo].[tbl_Login]
FOR UPDATE
AS
BEGIN
if((UPDATE(col1))
begin
print 'col1 is updated..'
end
if((UPDATE(col2))
begin
print 'col2 is updated..'
end
end




User defined functions
--------------------------
Create Function dbo.Factorial (@number int)
Returns int
As
BEGIN
Declare @finalResult int
If @number < 2
Select @finalResult = @number
Else
Select @finalResult = @number * dbo.factorial(@number - 1)

Return @finalResult
END
GO
Select dbo.Factorial (8)


Create Function dbo.TitleTable (@title_id Varchar(6))
Returns @ReturnTable Table (title_id Varchar(6),
title Varchar(80),
type Char(12))
As
BEGIN
Insert @ReturnTable
Select title_id, title, type From titles where title_id=@title_id
order by 1
Return
End
GO
Select * from dbo.TitleTable('BU1032')

No comments:

Post a Comment