RAM RAM Friends
Quickly Revise PL/SQL used in SQL Server 2000/2005/2008Following are the some of the important categories out of many for PL/SQL of SQL Server 2000/2005/2008
- some important(frequently used) Built-In Functions
- some important(frequently used) Built-in Stored Procedures
- Stored procedures
- Functions (UDF) (User Defined Functions) & their types
- Triggers & their types
- Views
Note:>>>> Below I have just mentioned their use & very less description as this is only for quickly revise
create proc InsertTable1
@name nvarchar(50)=null,
@image image=null,
@myimage2 image=null
as
begin
INSERT INTO Table1(name,image,myimage2) VALUES(@name,@image,@myimage2)
end
drop proc updatetable1
create proc UpdateTable1
@name nvarchar(50),
@image image,
@myimage2 image,
@id int
as
begin
if(@name is not null and @image is not null)
begin
UPDATE Table1
SET name=@name,
image=@image
end
end
USE arundb1
--==================
CREATE TABLE Countries
(
CountryId INT IDENTITY PRIMARY KEY,
CountryName NVARCHAR(50) UNIQUE
)
CREATE TABLE States
(
StateId INT IDENTITY PRIMARY KEY,
StateName NVARCHAR(50) NOT NULL,
CountryId INT FOREIGN KEY REFERENCES Countries(CountryId)
)
CREATE TABLE Cities
(
CityId INT IDENTITY PRIMARY KEY,
CityName NVARCHAR(50) NOT NULL,
StateId INT FOREIGN KEY REFERENCES States(StateId)
)
INSERT INTO Countries(CountryName)
SELECT 'Country1'
UNION ALL
SELECT 'Country2'
CREATE PROC FillCountries
as
begin
DECLARE @CountryNo INT
DECLARE @cmd NVARCHAR(200)
SELECT @CountryNo=1
WHILE(@CountryNo<5)
begin
SELECT @cmd='INSERT INTO Countries(CountryName) Values(''Cnt' + CONVERT(VARCHAR,@CountryNo) + ''')'
EXEC(@cmd)
SELECT @CountryNo=@CountryNo + 1
end
end
CREATE PROC FillStates
as
begin
DECLARE @StateNo INT
DECLARE @cmd NVARCHAR(200)
DECLARE @CountryId INT
DECLARE crsCountries CURSOR
FOR
SELECT CountryId FROM Countries
OPEN crsCountries
FETCH NEXT FROM crsCountries INTO @CountryId
WHILE(@@FETCH_STATUS=0) --CountryId
begin
SELECT @StateNo=1
WHILE(@StateNo<5) --insert 4 States
begin
SELECT @cmd='INSERT INTO States(CountryId,StateName) VALUES('+ CONVERT(VARCHAR,@CountryId) + ',''Cnt' + CONVERT(VARCHAR,@CountryId) + '_Stat' + CONVERT(VARCHAR,@StateNo) + ''')'
EXEC(@cmd)
SELECT @StateNo = @StateNo + 1
end
FETCH NEXT FROM crsCountries INTO @CountryId
end
CLOSE crsCountries
DEALLOCATE crsCountries
end
Create PROC FillCities
as
Begin
DECLARE @CountryId INT
DECLARE @StateId2 INT
DECLARE @CityNo INT
DECLARE @StateNo INT
DECLARE @cmd nvarchar(200)
DECLARE crsCountries CURSOR
FOR
SELECT CountryId FROM Countries
OPEN crsCountries
FETCH NEXT FROM crsCountries INTO @CountryId
WHILE(@@FETCH_STATUS=0) --Countryid
begin
DECLARE crsStates CURSOR
FOR
SELECT StateId FROM States
WHERE CountryId=@CountryId
OPEN crsStates
FETCH NEXT FROM crsStates INTO @StateId2
SELECT @StateNo=1
WHILE(@@FETCH_STATUS=0) -- StateId
begin
SELECT @CityNo=1
WHILE(@CityNo<5)--insert 4 Cities
begin
SELECT @cmd='INSERT INTO Cities(StateId,CityName) VALUES(' + CONVERT(VARCHAR,@StateId2) + ',''Cont' + CONVERT(VARCHAR,@CountryId) + '_St' + CONVERT(VARCHAR,@StateNo) + '_City_' + CONVERT(VARCHAR,@CityNo) + ''')'
print @cmd
exec(@cmd)
SELECT @CityNo=@CityNo +1
end
FETCH NEXT FROM crsStates INTO @StateId2
SELECT @StateNo=@StateNo+1
if(@StateNo>4)
SELECT @StateNo=1
end
close crsStates
DEALLOCATE crsStates
FETCH NEXT FROM crsCountries INTO @CountryId
end
CLOSE crsCountries
DEALLOCATE crsCountries
End
SELECT * FROM Countries
SELECT * FROM States
SELECT * FROM Cities
exec fillCountries
EXEC FillStates
EXEC FillCities
DROP PROC FillCountries
DROP PROC FillStates
DROP PROC FillCities
CLOSE crsCountries
DEALLOCATE crsCountries
DEALLOCATE @StateId
DELETE Cities
INSERT INTO Cities(CityName,StateId) VALUES('ArunCity',17)
sp_helptext usp_AddUpdateTaxes
'
- ' Alter PROCEDURE [DBO].[USP_ADDUPDATETAXES]
- '(
- ' @TAXID INT
- ' ,@TAXTYPE NVARCHAR(15)
- ' ,@TAXNAME NVARCHAR(MAX)
- ' ,@TAXVALUE NUMERIC
- ' ,@CUSTID INT
- ' ,@MINVAL NUMERIC
- ' ,@DESCR NVARCHAR(300)
- ' ,@UNIT NVARCHAR(20)
- ' ,@CURRENCY NVARCHAR(50)
- ' ,@TaxTypeHandling nvarchar(300)
- ')
- 'AS
- 'BEGIN
- ' SET NOCOUNT ON;
- '
- ' BEGIN TRAN
- ' IF @TAXID=0
- ' INSERT INTO TBL_TAXCHARGES(TAXTYPE,TAXNAME,TAXVALUE,CUSTID,MINVAL,DESCR,UNIT,CURRENCY,TaxTypeHandling) VALUES (@TAXTYPE,@TAXNAME,@TAXVALUE,@CUSTID,@MINVAL,@DESCR,@UNIT,@CURRENCY,@TaxTypeHandling)
- ' Else
- ' UPDATE TBL_TAXCHARGES SET TAXTYPE=@TAXTYPE,TAXNAME=@TAXNAME,TAXVALUE=@TAXVALUE,CUSTID=@CUSTID,MINVAL=@MINVAL,DESCR=@DESCR,UNIT=@UNIT,CURRENCY=@CURRENCY,TaxTypeHandling=@TaxTypeHandling WHERE TAXID=@TAXID
- ' -- SET @p_CompanyID_out = SCOPE_IDENTITY()
- '
- ' IF @@ERROR<>0
- ' ROLLBACK TRAN
- ' Else
- ' COMMIT TRAN
- '
- 'End
CREATE TABLE AddressBook
(
AddressId INT IDENTITY PRIMARY KEY,
MyName nvarchar(250) null,
ContactNo nvarchar(50) null,
Address nvarchar(300) null,
Photo image null
)
select * from AddressBook
CREATE PROCEDURE spInsertAddressBook
@Id int output,
@name nvarchar(250)=null,
@ContactNo nvarchar(50) =null,
@Address nvarchar(300)=null,
@Photo image=null
AS
BEGIN
INSERT INTO AddressBook(MyName,ContactNo,Address,Photo) VALUES(@name,@ContactNo, @Address,@Photo);
SELECT @Id=SCOPE_IDENTITY()
END
ALTER PROC spInsertAddressBook
@Id int Output,
@MyName nvarchar(250)=null,
@ContactNo nvarchar(50)=null,
@Address nvarchar(300)=null,
@Photo image=null
AS
BEGIN
INSERT INTO AddressBook(MyName,ContactNo,Address,Photo) VALUES(@MyName,@ContactNo,@Address,@Photo);
SELECT @Id=SCOPE_IDENTITY();
END
declare @myid int
EXEC spInsertaddressbook @myid output,'abc', 'a', 'a'
select @myid
--below is named paramerised sp calling
declare @myid int
exec spinsertaddressbook @id=@myid output,@myname='a',@contactno='aa'
select @myid
select * from AddressBook
-- Attaching a DB
use master ; EXEC sp_attach_db @dbname = 'ArunDB1',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\ArunDB1.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\ArunDB1_log.ldf'
create table myTable1
(
myId int Identity Primary Key,
FName nvarchar(200) null,
LName nvarchar(200) null,
Age numeric(3) null
)
insert into myTable1
select 'F_Ram1', 'L_Ram1',20
union all
select 'F_Ram2', 'L_Ram2', 21
union all
select 'F_Ram3', 'L_Ram3', 22
union all
select 'F_Ram4','L_Ram4',23
select * from mytable1
Select * from SysDataBases
select * from SysObjects
--P = SP
--FN = Scalar-valued UDF
--IF = Inline-Table Valued UDF
--TF = Multi-Statement Table-Valued UDF
--TR = Trigger
--V = View
--U == User Defined Tables
GO
if object_id('mysp1','P') is not null --mostly used now a days also working in SQL 2K but not at all places
--if exists(select name from sysobjects where name='mysp1' and type='P') --mostly used in SQL 2k but still working in SQL 2K5
drop proc mysp1
GO --needed since CREATE/ALTER must be first stmnt in batch query
create proc mysp1
@myFName nvarchar(200)='F%',
@myLName nvarchar(200)='L%'
as
begin
select * from mytable1 where FName like @myFName and LName like @myLName
end
exec mysp1
exec mysp1 '%','%'
exec mysp1 'F','L'
exec mysp1 'F%2'
--UDF User Defined functions---------------------
--1. Scalar valued UDF---------
Go
If object_id('GetMyAgeInYrs','FN') is not null
drop function GetMyAgeInYrs
Go
Create Function GetMyAgeInYrs (@myDOB datetime='07Apr1981', @CurrDt datetime='02May2011')
Returns int
as
begin
declare @myAge int
set @myAge=datediff(year,@myDob,@currdt)
return(@myage)
end
select GetMyAgeInYrs(default,default) as myAge --It shows error
--Scalar function name must be prefixed with either DBName.DBO.myFuntion1 or DBO.myFunction1
-- DBO is the owner name of db
select ArunDB1.DBO.GetMyAgeInYrs('07Apr1981',getdate()) as myAge
select dbo.getmyageinyrs(default,default) as myAgeInYrs
create function myNameWithAge (@myAge int=20)
returns nvarchar(200)
as
begin
declare @myName nvarchar(200)
set @myName=N'Arun Kakkar''s age is ' + convert(nvarchar(200),@myage) --If we not use N we got error
return(@myname)
end
select DBO.myNameWithAge(default) as Msg
select ArunDB1.DBO.myNameWithAge(30) as Msg
Alter Function myNameWithAge(@myAge int=30)
returns nvarchar(200)
as
Begin
declare @myName nvarchar(200)
set @myName=N'Arun Kakkar''s Age is=' + convert(nvarchar(200),@myAge)
return (@myName)
end
select DBO.myNameWithAge(29) as Msg
--2. Inline Table valued UDF--------------
Go
if exists(select name from sysobjects where name='myGetFName' and type='IF')
--if object_id('myGetFName','IF') is not null --working fine
drop function myGetFName
Go
create function myGetFName(@myFName nvarchar(200)='F%')
returns table
as
return(select * from myTable1 where FName like @myFName)
--in Select Query, Inline function acts as any table
select * from dbo.myGetFName(default)
select * from dbo.myGetFName('F%1')
select * from mytable1 where fname like 'F%1'
--3. Mulit-Statement Table-Valued UDF ------------------
Go
--if exists(select name from sysobjects where name='myGetFNameWithAge' and type='TF')
if object_Id('myGetFNameWithAge','TF') is not null
drop function myGetFNameWithAge
Go
Create Function myGetFNameWithAge(@myAgeAfterYrs int=20)
returns @myTable table
(
mySNo int Identity primary key ,
FName nvarchar(200) null,
NewAge numeric(4) null
)
as
Begin
insert into @myTable
select Fname,age from myTable1
update @myTable set NewAge= case
when NewAge>=20 and newAge<22 then
@myAgeAFterYrs
else
newAge
end
--return (@myTable)--It gives error
return
End
select * from dbo.myGetFNameWithAge(default)
select * from dbo.myGetFNameWithAge(29)
--***** Begin ****** Triggers **************************
Triggers
1. INSTEAD OF>> INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.
2. AFTER >>AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified only on tables.
Function AFTER trigger INSTEAD OF trigger
Applicability Tables Tables and views
Quantity per table or view Multiple per triggering action (UPDATE, DELETE, and INSERT) One per triggering action (UPDATE, DELETE, and INSERT)
Cascading references No restrictions apply Are not allowed on tables that are targets of cascaded referential integrity constraints.
Execution After:
• Constraint processing
• Declarative referential actions
• inserted and deleted tables creation
• The triggering action Before:
• Constraint processing
In place of:
• The triggering action
After:
• inserted and deleted tables creation
Order of execution First and last execution may be specified Not applicable
text, ntext, and image column references in inserted and deleted tables Not allowed Allowed
1. inserted and deleted Tables>> Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
a. The inserted and deleted tables are used primarily in triggers to:
i. Extend referential integrity between tables.
ii. Insert or update data in base tables underlying a view.
iii. Check for errors and take action based on the error.
iv. Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
b. The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
c. The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
d. An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
e. Each column in the inserted and deleted tables maps directly to a column in the base table.
f. The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:
i. CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
ii. AS
iii. SELECT EmployeeID, LastName, FirstName
iv. FROM Northwind.dbo.Employees
3. Syntax of Trigger creation
a. CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
--------------------
4. E.g 1 Use a trigger with a reminder message
a. USE pubs
b. --IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL –USED IN SQL Server 2005 onward
c. IF EXISTS (SELECT name FROM sysobjects
d. WHERE name = 'reminder' AND type = 'TR')
e. DROP TRIGGER reminder
f. GO
g. CREATE TRIGGER reminder
h. ON titles
i. FOR INSERT, UPDATE
j. AS RAISERROR (‘you are trying to do some unauthorized work’, 16, 10)
k. GO
5. E.g 2 Use a trigger with a reminder e-mail message
a. CREATE TRIGGER reminder
b. ON titles
c. FOR INSERT, UPDATE, DELETE
d. AS
e. EXEC master..xp_sendmail 'MaryM',
f. 'Don''t forget to print a report for the distributors.'
g. GO
6. E.g 3 Instead of trigger with view
a. CREATE TABLE BaseTable
b. (PrimaryKey int IDENTITY(1,1)
c. Color nvarchar(10) NOT NULL,
d. Material nvarchar(10) NOT NULL,
e. ComputedCol AS (Color + Material)
f. )
g. GO
h. --Create a view that contains all columns from the base table.
i. CREATE VIEW InsteadView
j. AS SELECT PrimaryKey, Color, Material, ComputedCol
k. FROM BaseTable
l. GO
m. --Create an INSTEAD OF INSERT trigger on the view.
n. CREATE TRIGGER InsteadTrigger on InsteadView
o. INSTEAD OF INSERT
p. AS
q. BEGIN
r. --Build an INSERT statement ignoring inserted.PrimaryKey and
s. --inserted.ComputedCol.
t. INSERT INTO BaseTable
u. SELECT Color, Material
v. FROM inserted
w. END
x. GO
y. --A correct INSERT statement supplying dummy values for the
z. --PrimaryKey and ComputedCol columns.
aa. INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
bb. VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
cc. --View the results of the INSERT statement.
dd. SELECT PrimaryKey, Color, Material, ComputedCol
ee. FROM InsteadView
7. E.g 4 used in SQL Server 2005
a. USE AdventureWorks2008R2;
b. GO
c. IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
d. DROP TRIGGER Purchasing.LowCredit;
e. GO
f. This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
g. when the credit rating of the specified vendor is set to 5 (below average).
h. CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
i. AFTER INSERT
j. AS
k. DECLARE @creditrating tinyint, @vendorid int;
l. IF EXISTS (SELECT *
m. FROM Purchasing.PurchaseOrderHeader p
n. JOIN inserted AS i
o. ON p.PurchaseOrderID = i.PurchaseOrderID
p. JOIN Purchasing.Vendor AS v
q. ON v.BusinessEntityID = p.VendorID
r. WHERE v.CreditRating = 5
s. )
t. BEGIN
u. RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
v. ROLLBACK TRANSACTION;
w. RETURN
x. END;
y. GO
8. E.g 5 Using a database-scoped DDL trigger
a. USE AdventureWorks2008R2;
b. GO
c. IF EXISTS (SELECT * FROM sys.triggers
d. WHERE parent_class = 0 AND name = 'safety')
e. DROP TRIGGER safety
f. ON DATABASE;
g. GO
h. CREATE TRIGGER safety
i. ON DATABASE
j. FOR DROP_SYNONYM
k. AS
l. RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
m. ROLLBACK
n. GO
o. DROP TRIGGER safety
p. ON DATABASE;
q. GO
9. e.g. 6. example of a single SQL Server trigger that handles all (3) three conditions of the trigger actions INSERT, UPDATE, and DELETE. This type of interface is sometimes needed when there is both a common configuration and a specific action to occur on an INSERT vs. UPDATE trigger. In these cases, to create a separate trigger for the UPDATE and the INSERT would become maintenance problem.
a. CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with caller queries SELECT statements.
-- If an update/insert/delete occurs on the main table, the number of records affected
-- should only be based on that table and not what records the triggers may/may not
-- select.
SET NOCOUNT ON;
--
-- Variables Needed for this Trigger
--
DECLARE @PACKLIST_ID varchar(15)
DECLARE @LINE_NO smallint
DECLARE @SHIPPED_QTY decimal(14,4)
DECLARE @CUST_ORDER_ID varchar(15)
--
-- Determine if this is an INSERT,UPDATE, or DELETE Action
--
DECLARE @Action as char(1)
DECLARE @Count as int
SET @Action = 'I' -- Set Action to 'I'nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
BEGIN
SET @Action = 'D' -- Set Action to 'D'eleted.
SELECT @Count = COUNT(*) FROM INSERTED
IF @Count > 0
SET @Action = 'U' -- Set Action to 'U'pdated.
END
if @Action = 'D'
-- This is a DELETE Record Action
--
BEGIN
SELECT @PACKLIST_ID =[PACKLIST_ID]
,@LINE_NO = [LINE_NO]
FROM DELETED
DELETE [dbo].[MyDataTable]
WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
END
ELSE
BEGIN
--
-- Table INSERTED is common to both the INSERT, UPDATE trigger
--
SELECT @PACKLIST_ID =[PACKLIST_ID]
,@LINE_NO = [LINE_NO]
,@SHIPPED_QTY =[SHIPPED_QTY]
,@CUST_ORDER_ID = [CUST_ORDER_ID]
FROM INSERTED
if @Action = 'I'
-- This is an Insert Record Action
--
BEGIN
INSERT INTO [MyChildTable]
(([PACKLIST_ID]
,[LINE_NO]
,[STATUS]
VALUES
(@PACKLIST_ID
,@LINE_NO
,'New Record'
)
END
else
-- This is an Update Record Action
--
BEGIN
UPDATE [MyChildTable]
SET [PACKLIST_ID] = @PACKLIST_ID
,[LINE_NO] = @LINE_NO
,[STATUS]='Update Record'
WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
END
END
--***** End ****** Triggers **************************
--*****Begin******** VIEW ********************************************************
--VIEW-->>Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:
-----------------------------------------------------------------------------
--To focus, simplify, and customize the perception each user has of the database.
--As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
--To provide a backward compatible interface to emulate a table whose schema has changed.
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ]
}
--------------
--When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.
----------------------------------------
--E.G 1 CREATING A SIMPLE view
USE AdventureWorks2008R2 ;
GO
--IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS --used in SQL Server 2000
-- WHERE TABLE_NAME = 'categories')
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
CREATE VIEW hiredate_view
AS
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
GO
------------------------------
--E.G 2 Using built-in functions within a view
USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL
DROP VIEW Sales.SalesPersonPerform ;
GO
CREATE VIEW Sales.SalesPersonPerform
AS
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)
GROUP BY SalesPersonID;
GO
---------------------------
--E.G. 3 Use @@ROWCOUNT function in a view
--This example uses the @@ROWCOUNT function as part of the view definition.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'myview')
DROP VIEW myview
GO
CREATE VIEW myview
AS
SELECT au_lname, au_fname, @@ROWCOUNT AS bar
FROM authors
WHERE state = 'UT'
GO
SELECT * FROM myview
---------------------------------------------
--***** END ******** VIEW ********************************************************
--**************** Introduction to Transaction Logs ***********************************
--Every SQL Server 2005 database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.
--The transaction log supports the following operations:
-------Recovery of individual transactions.
-------Recovery of all incomplete transactions when SQL Server is started.
-------Rolling a restored database, file, filegroup, or page forward to the point of failure.
-------Supporting transactional replication.
-------Supporting standby-server solutions.
--------------------------------------------------------------------
select * from myTable1
--Inserted>>Insert, Update
--Deleted>> Delete, Update
Go
--If exists(select Name from sysobjects where name='myTable1_AfterInsertTrig' and type='TR')
if object_Id('myTable1_AfterInsertTrig','TR') is not null
drop trigger myTable1_AfterInsertTrig
Go
Create Trigger myTable1_AfterInsertTrig
on myTable1
After Insert
as
Begin
Declare @FName nvarchar(200)
Declare @LName nvarchar(200)
Select @FName=FName, @LName=LName from Inserted
Set @FName=@FName + '_InsertTrigger'
insert into myTable1(Fname,Lname) values(@FName,@Lname)
End
insert into myTable1(FName,LName,age) values('Ram','Ram',30)
select * from myTable1
Go
if object_id('myTable1_InsteadOfDelTrig','TR') is not null
--if exists(select name from sysobjects where name='myTable1_InsteadOfDelTrig' and type='TR')
drop trigger myTable1_InsteadOfDelTrig
Go
Create Trigger myTable1_InsteadOfDelTrig
on myTable1
Instead of Delete
as
Begin
Declare @myFName nvarchar(200)
Declare @myLName nvarchar(200)
select @myFName=Fname,@myLName=LName from Deleted
set @myFName=@myFName + '_InsteadOfDelTrigger'
Insert into myTable1(FName,LName) values (@myFName,@myLName)
end
delete from myTable1 where myId=2
select * from myTable1
-- View ------------------
Go
If exists(select name from sysobjects where name='myTable1_View' and type='V')
--if object_id('myTable1_View','V') is not null
drop view myTable1_View
Go
create view myTable1_View
As
select Fname,age from myTable1
---------------
select * from myTable1_View
insert into myTable1_View values('Ram_View',30)
select * from myTable1
--triggeres set for base table also work on views
--insert stmnt apply on view also apply on base table
-------** Begin ************* Try Catch ****************************
-- Note: Try catch mentioned below work in SQL Server 2005
Go
if exists(select name from sysobjects where name='mySP_ErrorHandler' and type='P')
--if object_Id('mySP_ErrorHandler','P') is not null
Drop proc mySP_ErrorHandler
Go
create proc mySP_ErrorHandler
@myNumerator int=1, @myDenominator int=0
As
Begin
Begin try
--print @myNumerator/@myDenominator
select @myNumerator/@myDenominator as myDivisionResult
End try
Begin Catch
select Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_Procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
End Catch
End
exec mySP_ErrorHandler
exec mySP_ErrorHandler 5,2
exec mySP_ErrorHandler 5,0
Due to lack of time & of course some laziness, I would not able to complete this post in proper manner.
But as soon as I got opportunity, I would update this post as I think it would be very necessary to update this post to make it more understandable, readable etc.
Wish you a very Happy Programming.
But as soon as I got opportunity, I would update this post as I think it would be very necessary to update this post to make it more understandable, readable etc.
Wish you a very Happy Programming.
