SQL SERVER 2005 Study Material

SQL SERVER 2005 Study Material

Languages:

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT – Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction

CREATE Statement:
Syntax:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….
)

Example:
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
INSERT Statement:
Syntax:
The first form doesn’t specify the column names where the data will be inserted, only their values
INSERT INTO table_name
VALUES (value1, value2, value3,…)
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)
Example:
INSERT INTO Persons
VALUES (4,’Nilsen’, ‘Johan’, ‘Bakken 2’, ‘Stavanger’)
Insert Data Only in Specified Columns:
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, ‘Tjessem’, ‘Jakob’)
INSERT INTO SELECT :
Syntax:
INSERT INTO “table1” (“column1”, “column2”, …)
SELECT “column3”, “column4”, …
FROM “table2”
SELECT INTO Statement:
Syntax:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
The UPDATE Statement:
The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax:
UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value
Example:
UPDATE Persons
SET Address=’Nissestien 67′, City=’Sandnes’
WHERE LastName=’Tjessem’ AND FirstName=’Jakob’
The DELETE Statement
The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax:
DELETE FROM table_name
WHERE some_column=some_value
Delete All Rows:
DELETE FROM table_name
or
DELETE * FROM table_name
The ALTER TABLE Statement:
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
SQL ALTER TABLE Syntax:
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE Employee1
ADD Date DATETIME
To delete a column in a table, use the following syntax
ALTER TABLE table_name
DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype

ALTER TABLE Employee1
ALTER COLUMN Date VARCHAR

The DROP TABLE Statement:
The DROP TABLE statement is used to delete a table
DROP TABLE table_name
DQL(Data Query Language):
SELECT Statement Syntax:
SELECT DISTINCT TOP <Col1,Cl2,…>
FROM <Table Name>
JOIN <Table Name>
ON <Condition>
WHERE <Condition>
GROUP BY (Columna Name>
HAVING <Conditioin>
ORDER BY <Column Name>

CREATE TABLE Employee1
(
Eid INT,
Ename VARCHAR(50),
Mgr INT,
Sal INT
)
INSERT INTO Employee1 VALUES(101,’Shivu’,101,1000)
INSERT INTO Employee1 VALUES(102,’Ramu’,101,2000)
INSERT INTO Employee1 VALUES(103,’Brami’,102,3000)
INSERT INTO Employee1 VALUES(104,’Brami’,103,4000)
INSERT INTO Employee1 VALUES(105,’Shwetha’,102,5000)
INSERT INTO Employee1 VALUES(106,’Karnan’,104,6000)
INSERT INTO Employee1 VALUES(107,’Karnan’,103,7000)
INSERT INTO Employee1 VALUES(108,’Karnan’,105,8000)
INSERT INTO Employee1 VALUES(109,’vijay’,106,9000)
INSERT INTO Employee1 VALUES(110,’Ramu’,107,10000)
DISTINCT
SELECT DISTINCT Ename FROM Employee1

TOP
SELECT TOP 5 * FROM Employee1

WHERE :
SELECT * FROM Employee1 where Ename = ‘Karnan’
GROUP BY :
SELECT Ename,Sum(Sal)Sal FROM Employee1 GROUP BY Ename

HAVING:
SELECT Ename,Sum(Sal)Sal FROM Employee1 GROUP BY Ename having COUNT(*) > 1
ORDER BY :
SELECT Ename,Sum(Sal)Sal FROM Employee1 GROUP BY Ename ORDER BY Sal

TCL(Transactional Control Language):
ROLLABACK
COMMIT
CONSTRAINTS:
There are a number of different ways to implement constraints, but each of them falls into one of these three categories: entity, domain, and referential integrity constraints.
Domain Constraints: A Domain constraint deals with one or more columns. It is important to ensure that a particular column or a set of columns meets particular criteria. When you insert or update a row, the constraint is applied without respect to any other row in the table. The focus is on the data that is in the column. These kinds of constraints will resurface when we deal with Check constraints, Default constraints and rules and defaults.
Entity Constraints: Entity constraints are all about individual rows. This constraint ignores the column as a whole and focuses on a particular row. This can be best exemplified by a constraint that requires every row to have a unique value for a column or a combination of columns. This is to ensure that for a particular row, the same value does not already exist in some other row. We’ll see this kind of constraint in dealing with Primary key and Unique constraints.
Referential Integrity Constraints: Referential integrity constraints are created when a value in one column must match the value in another column. It can either be in the same table or more typically, a different table. For example, we are taking orders for a product, and we accept credit payment. But we will accept only a few standard credit card companies like Visa, MasterCard, Discover, and American Express. Referential integrity constraints allow us to build what we would call a domain table. A domain table is table whose sole purpose is to provide a limited list of acceptable values. In our case we have a CreditCard table with CreditCardID, and CreditCard as fields. We can then build one or more tables that reference the CreditCardID column of our domain table. With referential integrity, any table that is defined as referencing our CreditCard table will have to have a column that matches up to the CreditCardID column of our CreditCard table. For each row we insert into the referencing table, it will have a value that is in our domain list. We will see more of this when we learn about Foreign key constraints.

SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.Primary keys must contain unique values.A primary key column cannot contain NULL values.Each table should have a primary key, and each table can have only one primary key.
Example:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL PRIMARY KEY Constraint on ALTER TABLE
To create a PRIMARY KEY constraint on the “P_Id” column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
To DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the “P_Id” column when the “Orders” table is created:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
SQL FOREIGN KEY Constraint on ALTER TABLE
To create a FOREIGN KEY constraint on the “P_Id” column when the “Orders” table is already created, use the following SQL:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the “P_Id” column when the “Persons” table is created. The CHECK constraint specifies that the column “P_Id” must only include integers greater than 0.
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL CHECK Constraint on ALTER TABLE
To create a CHECK constraint on the “P_Id” column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=’Sandnes’)
To DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
DEFAULT Constraint:
SQL DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the “City” column when the “Persons” table is created:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
To DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
The following SQL enforces the “P_Id” column and the “LastName” column to not accept NULL values:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
SQL UNIQUE Constraint on CREATE TABLE:
The following SQL creates a UNIQUE constraint on the “P_Id” column when the “Persons” table is created:
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the “P_Id” column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id
To DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
JOINS:
INNER JOIN
This join returns rows when there is at least one match in both the tables.

OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN
This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,’First’
UNION ALL
SELECT 2,’Second’
UNION ALL
SELECT 3,’Third’
UNION ALL
SELECT 4,’Fourth’
UNION ALL
SELECT 5,’Fifth’
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,’First’
UNION ALL
SELECT 2,’Second’
UNION ALL
SELECT 3,’Third’
UNION ALL
SELECT 6,’Sixth’
UNION ALL
SELECT 7,’Seventh’
UNION ALL
SELECT 8,’Eighth’
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO

SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO

SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO

SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO

SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO

SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO

SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO

SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO

SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO

SELF JOIN:

OPERATORS:
Arithmetic operators:
Operator Meaning + (Add) Addition. – (Subtract) Subtraction. * (Multiply) Multiplication. / (Divide) Division.
Logical Operator
Operator Meaning AND TRUE if both Boolean expressions are TRUE. BETWEEN TRUE if the operand is within a range. IN TRUE if the operand is equal to one of a list of expressions. LIKE TRUE if the operand matches a pattern. NOT Reverses the value of any other Boolean operator. OR TRUE if either Boolean expression is TRUE.
AND
SELECT * FROM Employee1
WHERE ENAME = ‘Karnan’
AND Sal = 7000
OR
SELECT * FROM Employee1
WHERE ENAME = ‘Karnan’
OR ENAME = ‘Ramu’
BETWEEN:
SELECT * FROM Employee1
WHERE Sal BETWEEN 5000 and 9000

IN
SELECT * FROM Employee1 WHERE Mgr in (101,102)

LIKE
SELECT * FROM Employee1 WHERE Ename LIKE ‘K%’

SQL Wildcards
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description % A substitute for zero or more characters _ A substitute for exactly one character [charlist] Any single character in charlist [^charlist]
or
[!charlist] Any single character not in charlist

NOT
SELECT * FROM Employee1 WHERE Mgr in (101,102)

Comparison Operator
=, >, <, >=, <=, <>

SUB QUERY:
A subquery is a query that is nested inside a SELECT, or inside another subquery. Subquery is an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

Types Of Sub Queries:
Sub Query
Nested SubQuery
Co-Related SubQury
Sub Query:
A subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

Example
SELECT * FROM Employee1 WHERE Sal = (SELECT MAX(Sal) FROM Employee1)

Nested Sub Query:
Nested SubQuery is a it contain query within a sub query
Example:
SELECT MAX(Sal) FROM Employee1 WHERE Sal <
(
SELECT MAX(Sal) FROM Employee1 WHERE Sal < (SELECT MAX(Sal) FROM Employee1)
)

Co-Related Sub Query:
A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

Example:
SELECT * FROM Employee1 A WHERE 2 = (SELECT COUNT(DISTINCT Sal) FROM Employee1 B WHERE A.Sal <= B.Sal)
BUILT IN FUNCTIONS:

String Functions:
1. CHARINDEX string function takes 2 arguments. 1st argument specifies the character whose index is to be retrieved and 2nd argument takes as a string from which character index is carried out.
Example:
Select CHARINDEX (‘S’,’MICROSOFT SQL SERVER 2000′)
Result: 6

2. LEFT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns first characters of specified length starting from the left side of the string entered as 1st argument.
Example:
Select LEFT (‘MICROSOFT SQL SERVER 2000’,4)
Result: MICR

3. RIGHT string function takes 2 arguments. 1st argument takes as a string value and 2nd argument as integer value as length parameter. It returns last characters of specified length starting from the right side of the string entered as 1st argument.
Example:
Select RIGHT (‘MICROSOFT SQL SERVER 2000’,4)
Result: 2000

4. LEN string function takes 1 argument as string value and returns the length of entered string.
Example:
Select LEN (‘MICROSOFT SQL SERVER 2000’)
Result: 25

5. REPLACE string function takes 3 arguments.
1st argument as string value.
2nd argument is a part of string entered as 1st argument which is to be replaced.
3rd argument as a new string value that is to be placed at the place of 2nd argument.
Example:
Select REPLACE (‘MICROSOFT SQL SERVER 2000′,’MICROSOFT’,’MS’)
Result: MS SQL SERVER 2000
6.REPLICATE: Example: SELECT Replicate(‘Somu’,2)
Result: SomuSomu

7. SUBSTRING string function returns the sub string of specified length starting from the entered start position. It takes 3 arguments.
1st argument as string value.
2nd argument as integer specifying the start position.
3rd argument as integer specifying the length
Example:
Select SUBSTRING (‘MICROSOFT SQL SERVER 2000’, 11, 3)
Result: SQL
8. LOWER string function returns the lower case string whether the entered string has upper case letters. It takes 1 argument as string value.
Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING’)
Result: microsoft asp .net web hosting

9. UPPER string function returns the upper case string whether the entered string has lower case letters. It takes 1 argument as string value.
Example:
select LOWER(‘MICROSOFT ASP .NET WEB HOSTING with SQL Database’)
Result: MICROSOFT ASP .NET WEB HOSTING WITH SQL DATABASE
10. REVERSE string function returns the string in reverse order. It takes 1 argument as string value.
Example:
select REVERSE(‘ASP.NET’)
Result: TEN.PSA

11. LTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.
Example:
select LTRIM (‘ ASP ‘)
Result: ASP—–
blanks at the right side not removed.
12. RTRIM function returns the string by removing all the blank spaces at left side. It also takes 1 argument as string value.
Example:
select RTRIM (‘ ASP ‘)
Result: —–ASP
blanks at the left side not removed.

13. PATINDEX function returns the position of first occurrence of specified pattern in the provided string. It takes 2 arguments.
1st argument as string value specifying the pattern to match
2nd argument as string value specifying the string to compare.
Example:
select PATINDEX(‘%RO%’,’MICROSOFT’)
Results: 4
14. ASCII function returns the ASCII code value from the leftmost character specified character expression. It takes 1 argument as string/character expression.
Example:
select ASCII(‘A’)
Result: 65
Aggregation Function:
SELECT MAX(Sal) FROM Employee1 O/p – 10000
SELECT MIN(Sal) FROM Employee1 O/p – 1000
SELECT SUM(Sal) FROM Employee1 O/p – 55000
SELECT AVG(Sal) FROM Employee1 O/p – 5500
SELECT COUNT(*) FROM Employee1 O/p – 10
SELECT COUNT(Eid) FROM Employee1 O/p – 10
DATETIME Function:
DATEPART: Returns an integer that represents the specified datepart of the specified date
Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms Examples:
SELECT DATEPART(DD,GETDATE())
SELECT DATEPART(yy,GETDATE())
SELECT DATEPART(wk,GETDATE())
SELECT DATEPART(dw,GETDATE())

DATENAME:
Returns a character string representing the specified datepart of the specified date.

Example:

SELECT DATENAME(month, GETDATE())
SELECT DATENAME(mm, GETDATE())
SELECT DATENAME(m, GETDATE())
– It will return value = May
SQL DATEADD Function Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )

Example:
SELECT DATEADD(DD,1,GETDATE())
It’ll add 1 day to the current date

DATEDIFF:
SELECT DATEDIFF(DD,’01/01/2010′,’01/30/2010′)
O/P – 29
It’ll give the count of difference between two dates

DATE FORMATS Format # Query (current date: 12/30/2006) Sample 1 select convert(varchar, getdate(), 1) 12/30/06 2 select convert(varchar, getdate(), 2) 06.12.30 3 select convert(varchar, getdate(), 3) 30/12/06 4 select convert(varchar, getdate(), 4) 30.12.06 5 select convert(varchar, getdate(), 5) 30-12-06 6 select convert(varchar, getdate(), 6) 30 Dec 06 7 select convert(varchar, getdate(), 7) Dec 30, 06 10 select convert(varchar, getdate(), 10) 12-30-06 11 select convert(varchar, getdate(), 11) 06/12/30 101 select convert(varchar, getdate(), 101) 12/30/2006 102 select convert(varchar, getdate(), 102) 2006.12.30 103 select convert(varchar, getdate(), 103) 30/12/2006 104 select convert(varchar, getdate(), 104) 30.12.2006 105 select convert(varchar, getdate(), 105) 30-12-2006 106 select convert(varchar, getdate(), 106) 30 Dec 2006 107 select convert(varchar, getdate(), 107) Dec 30, 2006 110 select convert(varchar, getdate(), 110) 12-30-2006 111 select convert(varchar, getdate(), 111) 2006/12/30
CAST TYPING:
Converts an expression of one data type to another.
Tow Types:
CAST
CONVERT

Syntax for CAST:
CAST ( expression AS data_type)

Syntax for CONVERT:
CONVERT ( data_type, expression [ , style ] )

VARIABLES:

Declaring a variables
DECLARE @A INT

Assigning a values to variables
SET @A = (SELECT COUNT(*) FROM Employee1)

Print a Variable
SELECT @A

STORED PROCEDURES:
Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.

Benefits of Stored Procedures
* Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
* Reduced client/server traffic. If network bandwidth is a concern in your environment, you’ll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
* Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you’ll find the development cycle takes less time.
Example 1 – simple stored procedure
CREATE PROCEDURE Employee_Sp
AS
SELECT *
FROM Employee1
Execution Of Stored Procedures:
EXEC Employee_Sp
Example 2 – stored procedure with a parameter
This next example is a modification of the first example, but this time adding a parameter that is passed into the procedure to dynamically select the records. Instead of using CREATE PROCEDURE we are using ALTER PROCEDURE to modify the procedure that we created in Example 1 instead of dropping it first and then recreating it.

Alter PROCEDURE Employee_Sp
(
@Ename VARCHAR(50)
)
AS
SELECT *
FROM Employee1 WHERE Ename = @Ename

EXEC Employee_Sp ‘Karnan’
OR
EXEC Employee_Sp @Ename = ‘Karnan’

Example 3 – stored procedure with a parameter and output parameter
Alter PROCEDURE Employee_Sp
(
@Ename VARCHAR(50),
@Eid INT OUTPUT
)
AS
SELECT @Eid = Eid
FROM Employee1 WHERE Ename = @Ename
DECLARE @EmployeeId INT
EXEC Employee_Sp @Ename=’Karnan’, @Eid=@EmployeeId OUTPUT
SELECT @EmployeeId
To Display Dates Between two dates:

CREATE PROC DATEz
(
@Date1 DATETIME,
@Date2 DATETIME
)
AS
BEGIN
WHILE( @Date1 <= @Date2)
BEGIN

PRINT @Date1
SET @Date1 = @Date1 + 1
END
END

==========================================****========================================

User Defined Functions
User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.
Types of User Defined Functions:
There are three different types of User Defined Functions. Each type refers to the data being returned by the function. Scalar functions return a single value. In Line Table functions return a single table variable that was created by a select statement. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.
Scalar UDFs:
CREATE FUNCTION dbo.fn_RS_GetCntSunAndSat
(
@StartDate datetime,
@EndDate datetime
)
RETURNS INT
AS
BEGIN

DECLARE @NoOFDays INT
DECLARE @Holidays INT
DECLARE @Count INT

SET @NoOFDays = DATEDIFF(DD,@StartDate,@EndDate)
SET @Holidays = 0
SET @Count = 0

WHILE ( @Count <= @NoOFDays)
BEGIN
IF DATENAME(DW,DATEADD(DD,@Count,@StartDate)) = ‘Sunday’ OR DATENAME(DW,DATEADD(DD,@Count,@StartDate)) = ‘Saturday’
BEGIN
SET @Holidays = @Holidays + 1
END
SET @Count = @Count + 1
END
RETURN (@Holidays)
END

To Execute:
SELECT dbo.fn_RS_GetCntSunAndSat (’01/01/2010′,’01/30/2010′)
Inline Function:
CREATE FUNCTION Fn_Empz
(
@Eid INT
)
RETURNS TABLE
AS
RETURN
( SELECT Eid,Ename, Sal
FROM employee1
where Eid = @Eid
)
Execution:
SELECT * FROM Fn_Empz(101)

MultiStatement Function:
CREATE FUNCTION Fn_Empx
(
@EmpId INT
)
RETURNS @t TABLE( Eid INT,Ename VARCHAR(50) )
AS
BEGIN
INSERT INTO @t
SELECT Eid,Ename
FROM employee1
where Eid = @EmpId

RETURN
END

SELECT * FROM Fn_Empx(101)

==============================================******=========================
TRIGGERS:
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made ussing INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.

Types Of Triggers:
DDl Triggers
DML Triggers

Trigger to do not allow any transactions:
CREATE TRIGGER TRG2
On Employee1
FOR INSERT,DELETE,UPDATE
AS
BEGIN
PRINT ‘deletion not allowed’
ROLLBACK
END

Trigger do not allow to insert more than one record
CREATE TRIGGER Trg1
On Employee1
FOR Delete
AS
BEGIN
IF(SELECT COUNT(*) FROM Deleted) > 1
BEGIN
PRINT ‘Deletion Is Not allowed ‘
RollBack
END
END
Trigger do not allow the transactions in between 9AM to 6PM
CREATE TRIGGER Trg1
On Employee1
FOR Delete
AS
BEGIN
DECLARE @Time INT
SET @Time = DATEPART(HH,GETDATE())

IF @Time BETWEEN 9 AND 18
BEGIN
PRINT ‘Not allowed’
ROLLBACK
END
END

=======================================*****=================================

CURSORS:
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable
Five Steps while writing a Cursors
1. DECLARE CURSOR

2. OPEN

3. FETCH

4. @@FETCH_STATUS

5. CLOSE

6. DEALLOCATE
Example:
CURSOR:

DECLARE @MasterId Int
DECLARE @Name VARCHAR(50)

DECLARE merge_cursor CURSOR
DYNAMIC
FOR
SELECT ID, EName
FROM Emp25

OPEN merge_cursor

FETCH NEXT FROM merge_cursor
INTO @MasterId, @Name

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRANSACTION

UPDATE Emp25
SET EName = ‘Somu’
WHERE ID = @MasterId
AND id < 3

FETCH NEXT FROM merge_cursor INTO @MasterId, @Name
END

CLOSE merge_cursor
DEALLOCATE merge_cursor

CREATE TABLE FirstTable
(
Sub1 VARCHAR(50)
)

INSERT INTO FirstTable VALUES(‘C’)
INSERT INTO FirstTable VALUES(‘C++’)
INSERT INTO FirstTable VALUES(‘Java’)
CREATE TABLE SecondTable
(
Sub2 VARCHAR(50)
)

INSERT INTO SecondTable VALUES(‘.Net’)
INSERT INTO SecondTable VALUES(‘C++’)
INSERT INTO SecondTable VALUES(‘Oracle’)