Saturday, May 28, 2011

SQL SERVER – Get All the Information of Database using sys.databases



I suggest you can run this on your database and know unknown of your databases as well.

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(
SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(
SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(
SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(
SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME




Thursday, May 26, 2011

SQL Server : SCHEMABINDING



SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns.

What does this mean?

Well, to put it simply, once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view.  Examples of this would be removing columns or dropping tables that are specified in the view.



SQL SERVER – VIEWS


What is a View In SQL SERVER

  • A view can be thought of as either a virtual table or a stored query, like a real table, a view consists of a set of named columns and rows of data.
  • Unless a view is indexed, its data is not stored in the database as a distinct object.
  • What is stored in the database is a SELECT statement.
  • The result set of the SELECT statement forms the virtual table returned by the view.
  • A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
  • The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
  • A view acts as a filter on the underlying tables referenced in the view.
  • The query that defines the view can be from one or more tables or from other views in the current or other databases.
  • There are no restrictions on querying through views and few restrictions on modifying data through them.

Syntax: CREATE VIEW <view_name> [(column [,...n])]

[WITH <view_attribute> [,...n]]

AS select_statement

[WITH CHECK OPTION]

Under the view_attribute we have the following options:

[ENCRYPTION]

[SCHEMABINDING]

Types of Views:

  • Simple Views
  • Complex Views

Simple Views:

-          These Views as based upon a single table, which access the data from the single table.

-          They contain a Sub Query which retrieves the data from one base table.

CREATE VIEW SIMPLE_VIEW

AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP

-Once the view is created we can access the data from it as if it was a table as following:

SELECT * FROM SIMPLE_VIEW

SELECT EMPNO, ENAME, SAL, SAL*12 AS [ANNUAL SAL], DEPTNO FROM SIMPLE_VIEW

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

-We can also perform DML operations on the Simple Views which will effect on the base table.

INSERT INTO SIMPLE_VIEW VALUES(1234, 'SANTOSH', 4300, 20)

DELETE FROM SIMPLE_VIEW WHERE DEPTNO=20

UPDATE EMP SET SAL=5600 WHERE EMPNO=1001

-All the columns that are referenced in the view can be modified through the view.

-We cannot perform insert operations on the view if he view does not contain all the not null columns of the base table.

Complex Views:

-          If the View is based on multiple tables it is a complex view

-          If it is based on a single table with any of the following:

  • Group By Clause
  • Having Clause
  • Group Functions
  • Distinct Function
  • Function Calls

CREATE VIEW EMP_DEPT

AS

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC

FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

CREATE VIEW EMP_GRADE

AS

SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL

FROM EMP E INNER JOIN SALGRADE S

ON E.SAL BETWEEN S.LOSAL AND S.HISAL

CREATE VIEW EMP_MANAGERS

AS

SELECT E.ENAME + ' WORKS UNDER ' + M.ENAME

FROM EMP E INNER JOIN EMP M

ON E.MGR=M.EMPNO

CREATE VIEW EMP_SALARIES

AS

SELECT EMPNO, ENAME, DEPTNO, SAL AS MONTHLY, SAL*12 AS ANNUAL FROM EMP

CREATE VIEW EMP_DESIGNATIONS

AS

SELECT JOB FROM EMP WHERE DEPTNO=10

UNION

SELECT JOB FROM EMP WHERE DEPTNO=20

UNION

SELECT JOB FROM EMP WHERE DEPTNO=30

CREATE VIEW EMP_MAX_SAL

AS

SELECT DEPTNO, MAX(SAL) AS [HIGH SAL] FROM EMP GROUP BY DEPTNO

-If we want to perform manipulations on the Complex Views we have the following restrictions:

  • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  • The columns being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
    • An aggregate function
    • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators  amount to a computation and are also not updatable.
  • The columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

-We can also classify views as Updateable Views and Non Updateable Views:

-          A View, which allows manipulations on it, is known as Updateable View.

-          A View, which will not allow manipulations on it, is known as Non Updateable View.

With Check Option:

-          Forces all data modification statements executed against the view to follow the criteria set within select statement.

-          The Clause specifies that DML operations are not allowed on rows that the View cannot Select

-          When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

CREATE VIEW SALES_EMP

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

INSERT INTO SALES_EMP VALUES(1050, 'RAJU', 3500, 30)

-The above insert statement executes even if it does not satisfy the condition in the View, if this has to be restricted the view has to be created by using With Check Option clause.

ALTER VIEW SALES_EMP

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

WITH CHECK OPTION

-If we want to make any modifications to the existing view we can use the alter view statement.

View Attributes:

Encryption: After creating a view if we want to see the view definition that can be found in the SYSCOMMENTS System Table.

SELECT TEXT FROM SYSCOMMENTS WHERE OBJECT_NAME(ID)='SALES_EMP'

If we want to hide the definition from other persons we can use the Encryption option while creating the view or alter the view after creation to add the clause:

ALTER VIEW SALES_EMP

WITH ENCRYPTION

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

WITH CHECK OPTION

Schemabinding:

-          When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

-          The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

-          When you use SCHEMABINDING, the select statement must include the two-part names (schema.object) of tables that are referenced.

-          We need to specify the column names individual in the select statement, cannot use "*" in the select statement.

-          All referenced objects must be in the same database.

-          Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.

CREATE VIEW EMP_BIND

WITH SCHEMABINDING

AS

SELECT EMNO, ENAME, JOB, MGR FROM DBO.EMP

-After the view is created EMP table cannot be dropped are the column referred in the views cannot be altered using the alter command.



SQL SERVER – JOINS



By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how database should use data from one table to select the rows in another table.

A join condition defines the way two tables are related in a query by:

  • Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
  • Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Types of Joins:

    1. Equi-Joins
    2. Non Equi-Joins
    3. Self Joins
    4. Cartesian Joins
    5. Outer Joins
      • Left Outer Join
      • Right Outer Join

Equi-Joins:

It returns the specified columns from both the tables, and returns only the rows for which there is an equal value in the join column.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO

-The above statement is known, as old-style join statement, which will combine the tables basing on equality condition i.e. the Deptno column in the Emp table, has to have an exact match of Deptno in the Dept table, then these 2 rows combine and get retrieved. In the new-style we call this as Inner Join where we write the statement as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

-In the same way if we want to combine multiple table in old-style we write following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC,

DD.DID, DD.COMMENTS

FROM EMP E, DEPT D, DEPTDETAILS DD

WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO=DD.DEPTNO

-The same statement in the new-style we write as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC,

DD.DID, DD.COMMENTS

FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

INNER JOIN DEPTDETAILS DD

ON D.DEPTNO=DD.DEPTNO

Non Equi-Joins:

You can also join values in two columns that are not equal. The same operators and predicates used for equi-joins can be used for not-equi joins.

SELECT

E.EMPNO, E.ENAME, E.SAL,

S.SALGRADE, S.LOSAL, S.HISAL

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

-We can write the above statement using inner join in the new style as following:

SELECT

E.EMPNO, E.ENAME, E.SAL,

S.SALGRADE, S.LOSAL, S.HISAL

FROM EMP E INNER JOIN SALGRADE S

ON E.SAL BETWEEN S.LOSAL AND S.HISAL

Self Join:

If a table has a reflexive relationship in the database, you can join it to itself automatically which is known as self join.

SELECT

DISTINCT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO

FROM EMP E, EMP M

WHERE E.EMPNO=M.MGR

-We can write the above statement using inner join in the new style as following:

SELECT

DISTINCT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO

FROM EMP E INNER JOIN EMP M

ON E.EMPNO=M.MGR

Cartesian Join:

A Cartesian join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. This is also known as cross-join. However, if a WHERE clause is added, the cross join behaves as an inner join.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

-We can write the above statement in the new style as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E CROSS JOIN DEPT D

Outer Join:

By default, when we join multiple tables using inner join what we get is the matching data from the tables, if we want to include data rows in the result set that do not have a match in the joined table, we can us outer join.

The old-style of outer joins have been classified into 2 types as Left Outer Join and Right Outer Join.

We use Left Outer Join to get the matching information plus unmatched information from left hand side table, in the same way we use Right Outer Join to get the matching information plus unmatched information from right hand side table.

Left hand side table and right hand side tables are referred in the order we write in the from clause, first table is LHS table and second table is RHS table.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO=*D.DEPTNO

-In the above case we get the matching information plus unmatched information from Dept table.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO*=D.DEPTNO

-In the above case we get the matching information plus unmatched information from Emp table.

-Suppose we have unmatched information in both the sides we cannot retrieve it at the same time to over come this in the new-style of join statement they have introduced Full Outer join. So the new-style supports use the following:

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

-Use Left Outer Join to get the unmatched information from left hand side table as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E LEFT OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

-Use Right Outer Join to get the unmatched information from right hand side table as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E RIGHT OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

-Use Full Outer Join to get the unmatched information from both the tables as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E FULL OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

Finally concluding in the new-style we have only 3 types of joins those are Inner Joins, Cross Joins and Outer Joins in the place of Equi-Joins, Non Equi-Joins, Self Joins, Cartesian Joins and Outer Joins which are present in the old-style.




Wednesday, May 25, 2011

SQL SERVER – TRIGGERS



  • Microsoft SQL Server 2005 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers.
  • A trigger is a special type of stored procedure that automatically takes effect when a language event executes.
  • SQL Server includes two general types of triggers: DML triggers and DDL triggers.
  • DDL triggers are new to SQL Server 2005. These triggers are invoked when a data definition language (DDL) event takes place in the server or database.
  • DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view.
  • A DML trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DML triggers are useful in these ways:

  • They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
  • They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.
  • Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.
  • They can evaluate the state of a table before and after a data modification and take actions based on that difference.
  • Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

Types of DML Triggers:

AFTER Triggers: 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 Microsoft SQL Server. AFTER triggers can be specified only on tables.

INSTEAD OF Triggers: 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.

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. DDL triggers execute in response to a variety of data definition language (DDL) events. These are primarily CREATE, ALTER, and DROP statements. DML and DDL triggers can be created in the SQL Server 2005 Database Engine directly from Transact-SQL. SQL Server allows for creating multiple triggers for any specific statement.

Syntax: CREATE TRIGGER trigger_name

ON  table | view

[ WITH ENCRYPTION ]

FOR | AFTER | INSTEAD OF

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]

AS

BEGIN

sql_statements

END

table | view: Is the table or view on which the DML trigger is executed and is sometimes referred to as the trigger table or trigger view. A view can be referenced only by an INSTEAD OF trigger.

WITH ENCRYPTION: Encrypts the text of the CREATE TRIGGER statement.

AFTER: Specifies that the DML trigger be fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified. AFTER triggers cannot be defined on views.

INSTEAD OF: Specifies that the DML trigger be executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

[ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ]: Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. At least one option must be specified. Any combination of these options in any order is allowed in the trigger definition. For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

-A Trigger that will restrict the operations to be performed before 9 A.M and after 5 P.M

CREATE TRIGGER EMP_TRG

ON EMP AFTER INSERT, UPDATE, DELETE

AS

BEGIN

DECLARE @DT INT

SET @DT=DATENAME(HH, GETDATE())

IF @DT NOT BETWEEN 9 AND 16

BEGIN

ROLLBACK

RAISERROR('CANNOT PERFORM DML OPERATIONS NOW', 15, 1)

END

END

-After the trigger is created try to perform any DML Operations on the EMP table before 9 A.M and after 5 P.M the Trigger will fire and restrict the operations.

  • When we try to perform any DML Operation on a table when a trigger is present on it the values of the DML statement will be captured in the trigger inside 2 Magic Tables Inserted and Deleted that have the same structure of the table.
  • 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.
  • The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added at the same time 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.
  • 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.

-A trigger that will convert the DName and Loc into upper case when the user insert in lower case.

CREATE TRIGGER DEPT_CONVERT_TRG

ON DEPT AFTER INSERT

AS

BEGIN

DECLARE @DEPTNO INT

DECLARE @DNAME VARCHAR(50)

DECLARE @LOC VARCHAR(50)

SELECT @DEPTNO=DEPTNO, DNAME=@DNAME, @LOC=LOC FROM INSERTED

UPDATE DEPT SET DNAME=UPPER(@DNAME), LOC=UPPER(@LOC) WHERE DEPTNO=@DEPTNO

END

-To test the trigger execute the following statement which will convert the data into upper case in the table:

INSERT INTO DEPT VALUES(50, 'research' ,'hyderabad')

-The above trigger can be written in the following way also:

CREATE TRIGGER DEPT_CONVERT_TRG2

ON DEPT INSTEAD OF INSERT

AS

BEGIN

INSERT INTO DEPT

SELECT DEPTNO, UPPER(DNAME), UPPER(LOC) FROM INSERTED

END

-A Trigger which will generate a unique Deptno when the user inserts a record into the dept table only by specifying DName and Loc when a primary key constraint is present on the Deptno column.

CREATE TRIGGER DEPT_GENERATE_TRG

ON DEPT INSTEAD OF INSERT

AS

BEGIN

DECLARE @DEPTNO INT

SELECT @DEPTNO=DEPTNO FROM INSERTED

IF @DEPTNO IS NULL

SELECT @DEPTNO=ISNULL(MAX(DEPTNO), 0) + 10 FROM DEPT

INSERT INTO DEPT SELECT @DEPTNO, DNAME, LOC FROM INSERTED

END

-To test the following Trigger execute the following statement:

INSERT INTO DEPT(DNAME, LOC) VALUES('RESEARCH', 'HYDERABAD')

-A program which will restrict the Delete operation if the Job of the person is Manager.

ALTER TRIGGER EMP_DELETE_TRG

ON EMP AFTER DELETE

AS

BEGIN

DECLARE @JOB VARCHAR(50)

SELECT @JOB=JOB FROM DELETED

IF @JOB='MANAGER'

BEGIN

ROLLBACK

RAISERROR('CANNOT DELETE MANAGER FROM THE TABLE', 15, 1)

END

END

-To test the following Trigger execute the following statement:

DELETE FROM EMP WHERE EMPNO=1002

A Trigger which will restrict to update the Salary of the Employee if the New Salary is less than the Old Salary.

CREATE TRIGGER EMP_UPDATE_TRG

ON EMP AFTER UPDATE

AS

BEGIN

DECLARE @OLDSAL MONEY

DECLARE @NEWSAL MONEY

SELECT @OLDSAL=SAL FROM DELETED

SELECT @NEWSAL=SAL FROM INSERTED

IF @OLDSAL > @NEWSAL

BEGIN

ROLLBACK

RAISERROR('NEW SAL CANNOT BE LESS THAN OLD SAL', 15, 1)

END

END

NESTED TRIGGERS: Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers.

-A Trigger which will fire when a record is inserted into the Emp table which verifies whether the given Deptno is present in the Dept table or not if not it will insert a record into it.

CREATE TRIGGER EMP_NESTED_TRG

ON EMP AFTER INSERT

AS

BEGIN

DECLARE @DEPTNO INT

SELECT @DEPTNO=DEPTNO FROM INSERTED

IF NOT EXISTS(SELECT * FROM DEPT WHERE DEPTNO=@DEPTNO)

INSERT INTO DEPT VALUES(@DEPTNO, NULL, NULL)

END

-A Trigger which will fire when a record is inserted into the Dept table which verifies whether the given Deptno is present in the DeptDetails table or not if not it will insert a record into it.

CREATE TRIGGER DEPT_NESTED_TRG

ON DEPT AFTER INSERT

AS

BEGIN

DECLARE @DEPTNO INT

DECLARE @DID INT

SELECT @DEPTNO=DEPTNO FROM INSERTED

IF NOT EXISTS(SELECT * FROM DEPTDETAILS WHERE DEPTNO=@DEPTNO)

BEGIN

SELECT @DID=MAX(DID)+ 1 FROM DEPTDETAILS

INSERT INTO DEPTDETAILS VALUES(@DID, @DEPTNO, NULL)

END

END

-After creating the 2 Triggers if we try to insert a record into the Emp table with a Deptno not present in the Dept table it will insert a record into the Dept table which will internally check whether the Deptno is present in the DeptDetails table or not and inserts a records into it if not present.

Instead of Triggers on Complex Views which are not updatable:

CREATE VIEW EMP_DEPT

AS

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

-After creating the view try to execute the following insert which will fail because complex views are by default non updatable:

INSERT INTO EMP_DEPT VALUES(1100, 'RAJU', 4500, 50, 'IT', 'BANGLORE')

-If the above statement has to execute we need to define a Instead of trigger on the view so that the View become updatable.

CREATE TRIGGER VIEW_INSERT_TRG

ON EMP_DEPT INSTEAD OF INSERT

AS

BEGIN

INSERT INTO EMP(EMPNO, ENAME, SAL, DEPTNO)

SELECT EMPNO, ENAME, SAL, DEPTNO FROM INSERTED

INSERT INTO DEPT(DEPTNO, DNAME, LOC)

SELECT DEPTNO, DNAME, LOC FROM INSERTED

END

-In the same way a trigger which will allow delete operations to be performed on the View:

CREATE TRIGGER VIEW_DELETE_TRG

ON EMP_DEPT INSTEAD OF DELETE

AS

BEGIN

DECLARE @DEPTNO INT

DECLARE @COUNT INT

SELECT @DEPTNO=DEPTNO FROM DELETED

SELECT @COUNT=COUNT(*) FROM EMP WHERE DEPTNO=@DEPTNO

DELETE FROM EMP WHERE DEPTNO=@DEPTNO

IF @COUNT=1

DELETE FROM DEPT WHERE DEPTNO=@DEPTNO

END




SQL SERVER – Functions



  • A Function is also Stored Block of code similar to a Procedure.
  • A Function is a Block of Code which will return only a single value.
  • A Function is not a stand alone executable like a Procedure it can be executed in some other context also.
  • A Function can be used in a Select Statement.
  • Modifications to database tables, operations on cursors that are not local to the function are examples of actions that cannot be performed in a function.
  • Try and Catch Statements cannot be used in the Functions.
  • A user-defined function takes zero or more input parameters and returns either a scalar value or a table; a function can have a maximum of 1024 input parameters.
  • User-defined functions do not support output parameters.
  • When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value.

Functions are of 3 types:

1. Scalar Functions

2. Inline Table-valued Functions

3. Multistatement Table-valued Functions

Scalar Functions: Functions are scalar-valued if the RETURNS clause specifies one of the scalar data types.

Syntax: CREATE FUNCTION <function_name>

( [ @parameter_name [ AS ] data_type [ = default ] [ ,...n ] ] )

RETURNS data_type

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

function_options can be any of these two:

  1. Encryption
  2. Schemabinding

ENCRYPTION: Indicates that the Database Engine encrypts the catalog view columns that contain the text of the CREATE FUNCTION statement.

SCHEMABINDING: Specifies that the function is bound to the database objects that it references. The binding of the function to the objects it references is removed only when one of the following actions occurs:

  • The function is dropped.
  • The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

-A Function that takes the Empno and Returns the total salary of the employee.

CREATE FUNCTION GET_TSAL (@EMPNO INT)

RETURNS MONEY

AS

BEGIN

DECLARE @TSAL MONEY

SELECT @TSAL=SAL + ISNULL (COMM, 0) FROM EMP WHERE EMPNO=@EMPNO

RETURN @TSAL

END

Syntax for Calling a Scalar Function:

SELECT <owner>.<function_name>( <list of values> )

Calling the above function:

SELECT DBO.GET_TSAL(1005)

Inline Table-valued Functions: These functions can return a table as an output. In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables.

Syntax: CREATE FUNCTION <function_name>

( [ @parameter_name [ AS ] data_type [ = default ] [ ,...n ] ] )

RETURNS TABLE

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN [ ( ] select_stmt [ ) ]

END

-A function which takes the deptno and returns the list of employees working in it by joining Emp and Dept tables.

CREATE FUNCTION GET_ED_DATA(@DEPTNO INT)

RETURNS TABLE

AS

RETURN (SELECT E.EMPNO, ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE E.DEPTNO=@DEPTNO)

Syntax for Calling a Table Valued Functions:

SELECT < * | <collist> FROM <function_name>( <list of values> )

Calling the above function:

SELECT * FROM GET_ED_DATA(10)

SELECT EMPNO, ENAME, DEPTNO, DNAME FROM GET_ED_DATA(20)

Multistatement Table-valued Functions: These function are same as Inline Table-valued but the body of this functions can contain multiple statement in it and the structure of the table can be defined by us.

Syntax: CREATE FUNCTION <function_name>

( [ @parameter_name [ AS ] data_type [ = default ] [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH <function_option> [ ,...n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

-A function which, takes the Empno and calculates the Total Salary and Annual Salary of the employee and returns them.

CREATE FUNCTION GET_EMPDATA(@EMPNO INT)

RETURNS @MYTABLE TABLE(TOTSAL MONEY, ANNSAL MONEY)

AS

BEGIN

DECLARE @SAL MONEY, @COMM MONEY

DECLARE @TSAL MONEY, @ASAL MONEY

SELECT @SAL=SAL, @COMM=COMM FROM EMP WHERE EMPNO=@EMPNO

SET @TSAL=@SAL + ISNULL(@COMM, 0)

SET @ASAL=(@SAL + ISNULL(@COMM, 0)) * 12

INSERT INTO @MYTABLE VALUES(@TSAL, @ASAL)

RETURN

END

Calling the above function:

SELECT * FROM GET_ EMPDATA(1005)




SQL SERVER – Procedures



  • A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.
  • Procedures can be created for permanent use or for temporary use within a session, local temporary procedure, or for temporary use within all sessions, global temporary procedure.
  • Stored procedures can also be created to run automatically when an instance of SQL Server starts.

Syntax:

CREATE | ALTER PROCEDURE <procedure_name>

[ ( @parameter1 <data_type> [ = default ] [ OUT | OUTPUT ],

@parameter2 <data_type> [ = default ] [ OUT | OUTPUT ],

…………………….

@parametern <data_type> [ = default ] [ OUT | OUTPUT ] ) ]

[ WITH <procedure_options> ]

AS

BEGIN

<statements>

END

ALTER:

Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement. ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers.

Procedure Options:

The Procedure provide to options that can be used while creating the procedures. They are:

  1. Encryption
  2. Recompile

RECOMPILE:

Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. Use the RECOMPILE query hint when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.

Important: Because the SQL Server 2005 query optimizer typically selects the best execution plan for a query, we recommend that hints, including <query_hint>, be used only as a last resort by experienced developers and database administrators.

ENCRYPTION:

Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text.

-Procedure contains 2 parts in it:        1. Header        2. Body

-Header part is the content above the AS keyword.

-Body part is the content below the AS keyword.

Passing Parameters to Procedures:

As if we are passing parameters to functions in languages, we can also pass parameters to Procedures. They are the means to pass a value to the procedure or returns from a procedure.

Parameter Modes:

These will specify whether ther parameter is passed into the procedure or returned out of the procedure. SQL Server supports to Parameter Modes:

  • IN MODE       (DEFAULT)
  • OUT OR OUTPUT MODE

IN MODE:

Passes a value into the procedure for execution, this is best suitable for constants & expressions. The value of it can be changed with in the program but cannot be returned. It is the default mode if nothing is specified

OUT MODE:

Passes a value back from the program after the execution of the procedure.

The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters

Syntax for executing the Procedure:

EXEC | EXECUTE [   [@parameter=] <value> [OUTPUT] [DEFAULT] [,…n]   ]

A Simple Procedure:

CREATE PROCEDURE PROC1

AS

BEGIN

PRINT 'MY FIRST PROCEDURE'

END

-Executing the above procedure:

EXEC PROC1 OR EXECUTE PROC1

A Procedure which accepts arguments:

ALTER PROCEDURE PROC2(@X INT, @Y INT)

AS

BEGIN

DECLARE @Z INT

SET @Z=@X+@Y

PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)

END

-Executing the above procedure in 2 ways:

  1. EXEC PROC2 100, 50
  2. EXEC PROC2 @X=100, @Y=50

A Procedure with Default Values:

CREATE PROCEDURE PROC3(@X INT = 100, @Y INT)

AS

BEGIN

DECLARE @Z INT

SET @Z=@X+@Y

PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)

END

-Executing the above procedure:

  1. EXEC PROC3 200, 25
  2. EXEC PROC3 @X=200, @Y=25
  3. EXEC PROC3 @X=DEFAULT, @Y=25
  4. EXEC PROC3 @Y=25

-In the 3rd and 4th case it uses the default value of 100 to the varibale X which has been given while creating the procedure.

A Procedure with OUTPUT Parameter:

CREATE PROCEDURE PROC4(@X INT, @Y INT, @Z INT OUTPUT)

AS

BEGIN

SET @Z=@X+@Y

END

- Executing the above procedure:

DECLARE @A INT

EXECUTE PROC4 500, 250, @A OUTPUT

PRINT @A

-A Procedure for Inserting values into the Emp Table:

CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)

As

Begin

INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)

End

- Executing the above Procedure:

EXEC Insert_Emp 1016, 'Sudhakar', 2500, 10

-A Procedure for Inserting values into the Emp Table but with Validations:

-This is same as the previous one but with the following validations present in it:

-Empno cannot be NULL value.

-Empno cannot be duplicated.

-Salary cannot be less than 2500.

-Deptno should be present in the Dept Table.

CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)

As

Begin

IF @Empno IS NULL

Begin

Print 'Empno cannot be NULL'

Return

End

IF Exists(SELECT * FROM Emp WHERE Empno=@Empno)

Begin

Print 'Empno cannot be Duplicated'

Return

End

IF @Sal<2500 Begin

Print 'Salary cannot be less than 2500'

Return

End

IF Not Exists(SELECT * FROM Dept WHERE Deptno=@Deptno)

Begin

Print 'Deptno not found in the Dept Table'

Return

End

INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)

End

-A Procedure which takes the Empno and returns the Provident Fund and Professional Tax at 12% and 5% respectively on the Salary.

CREATE PROCEDURE Deductions(@Empno int, @PF money OUTPUT, @PT money OUTPUT)

As

Begin

Declare @Sal Money

SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno

SET @PF=@Sal * 0.12

SET @PT=@Sal * 0.05

End

-Executing the above Procedure:

Declare @VPF money, @VPT money

EXEC Deductions 1005, @VPF OUTPUT, @VPT OUTPUT

Print @VPF

Print @VPT

-A Procedure which takes the Empno and prints the Net Salary of the Employee.

CREATE PROCEDURE Net_Sal(@Empno int)

As

Begin

Declare @VSal money, @NSal money, @VPF money, @VPT money

EXEC Deductions @Empno, @VPF OUTPUT, @VPT OUTPUT

SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno

SET @NSal = @VSal – @VPF – @VPT

Print 'Net Salary of the Employee is: ' + Cast(@NSal as Varchar)

End

-Executing the above Procedure:

EXEC Net_Sal 1005

-A Procedure which will Insert values into the Dept table by generating a unique Deptno.

CREATE PROCEDURE Insert_Dept(@Dname varchar(50), @Loc varchar(50))

As

Begin

Declare @Deptno int

Select @Deptno = ISNULL(MAX(Deptno), 0) + 10 FROM Dept

INSERT INTO Dept Values (@Deptno, @Dname, @Loc)

End

-Executing the above Procedure:

EXEC Insert_Dept 'Research', 'Hyderabad'

-A Procedure which is used from transferring amount from one account to the other within the Bank table:

CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)

As

Begin

UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID

UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID

End

-Executing the above Procedure:

EXEC Funds_Transfer 101, 102, 500

-          In the above case if the SrcID or DestID are not present in the table then  it will deduct the amount from the other or add the amount from the other to avoid this we need to use transaction management.

-          To manage the transaction first we need to identify which statement is executed and which failed for this we use the function @@ROWCOUNT.

-          @@ROWCOUNT returns the number of rows affected by the last statement.

-Managing Transactions in the Procedure:

CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)

As

Begin

Declare @Count1 int, @Count2 int

Begin Transaction

UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID

Set @Count1=@@ROWCOUNT

UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID

Set @Count2=@@ROWCOUNT]

IF @COUNT1=@COUNT2

Begin

COMMIT

PRINT 'TRANSACTION COMMITED'

End

ELSE

Begin

ROLLBACK

PRINT 'TRANSACTION ROLLED BACK'

End

End

Handling Errors in Procedures:

-          In SQL Server when a error occurs, the statement that caused the error is terminated, but the execution of the stored procedure or batch continues.

-          When stored procedures and batches are executed within the scope of a TRY block, batch abort errors can be handled by the TRY…CATCH construct.

-          Errors in Transact-SQL code can be processed using a TRY…CATCH construct similar to the exception-handling features of the languages.

-          A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block.

-          When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.

-          After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement.

-          If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.

-          Transact-SQL statements in the TRY block following the statement that generates an error will not get executed.

-          If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement.

-          If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.

-          A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement.

-          One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.

-          A CATCH block must follow a TRY block immediately.

-          A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement.

-          In Transact-SQL, each TRY block is associated with only one CATCH block.

-A Procedure which can cause Error:

CREATE PROCEDURE Div(@X int, @Y int)

As

Begin

Declare @Z int

SET @Z=0

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

END

-Executing the above procedure:

EXEC DIVX 100, 20

EXEC DIVX 100, 0

-The first execution will print the result of 5 but the second time execution will raise an error because we cannot divide a number by zero, in this case still it will try to print the result as 0, because even if the error is encountered it will not stop the execution of the program, if we want to stop the execution of the program when an error raises the code has to be written in the following way:

CREATE PROCEDURE Div(@X int, @Y int)

As

Begin

Begin Try

Declare @Z INT

SET @Z=0

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

End Try

Begin Catch

Print Error_Message()

End Catch

End

-Executing the above procedure:

EXEC DIVX 100, 20

EXEC DIVX 100, 0

-          Every error has 4 properties to it, they are:

  • Msg id
  • Msg str
  • Severity
  • State

For Example try the following statement:

Print 100/0

-This will display the following error message:

Msg 8134, Level 16, State 1,

Divide by zero error encountered.

-In this the Msg id is 8134, Msg str is "Divide by zero error encountered", Severity Level is 16 and State is 1.

Msg id: ID of the message, which is unique across server. Message IDs less than 50000 are system messages.

Msg str: Error message that has to be displayed when the error raises.

Severity Level: Severity level that is associated with the error. Severity levels can range between 0 and 25. Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

State: Is an arbitrary integer from 1 through 127. If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

Raising Errors Manually: We can also raise errors manually at some required situations. It is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. For raising an error manually we use the Raiserror Statement.

It generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

RAISERROR can return either:

  • A user-defined error message that has been created using the sp_addmessage system stored procedure.
  • A message string specified in the RAISERROR statement.

RAISERROR can also:

  • Assign a specific error number, severity, and state.
  • Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
  • Substitute argument values into the message text, much like the C language printf function.

Syntax: RAISERROR ( msg_id | msg_str | @local_variable, severity, state

[, argument [ ,...n ] ] )

[ WITH option [ ,...n ] ]

msg_id: Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

Procedures:

  • A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.
  • Procedures can be created for permanent use or for temporary use within a session, local temporary procedure, or for temporary use within all sessions, global temporary procedure.
  • Stored procedures can also be created to run automatically when an instance of SQL Server starts.

Syntax:

CREATE | ALTER PROCEDURE <procedure_name>

[ ( @parameter1 <data_type> [ = default ] [ OUT | OUTPUT ],

@parameter2 <data_type> [ = default ] [ OUT | OUTPUT ],

…………………….

@parametern <data_type> [ = default ] [ OUT | OUTPUT ] ) ]

[ WITH <procedure_options> ]

AS

BEGIN

<statements>

END

ALTER: Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement. ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers.

Procedure Options: The Procedure provide to options that can be used while creating the procedures. They are:

  1. Encryption
  2. Recompile

RECOMPILE: Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. Use the RECOMPILE query hint when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.

Important: Because the SQL Server 2005 query optimizer typically selects the best execution plan for a query, we recommend that hints, including <query_hint>, be used only as a last resort by experienced developers and database administrators.

ENCRYPTION: Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text.

-Procedure contains 2 parts in it:        1. Header        2. Body

-Header part is the content above the AS keyword.

-Body part is the content below the AS keyword.

Passing Parameters to Procedures: As if we are passing parameters to functions in languages, we can also pass parameters to Procedures. They are the means to pass a value to the procedure or returns from a procedure.

Parameter Modes: These will specify whether ther parameter is passed into the procedure or returned out of the procedure. SQL Server supports to Parameter Modes:

  • IN MODE       (DEFAULT)
  • OUT OR OUTPUT MODE

IN MODE: Passes a value into the procedure for execution, this is best suitable for constants & expressions. The value of it can be changed with in the program but cannot be returned. It is the default mode if nothing is specified

OUT MODE: Passes a value back from the program after the execution of the procedure.

The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters

Syntax for executing the Procedure:

EXEC | EXECUTE [   [@parameter=] <value> [OUTPUT] [DEFAULT] [,…n]   ]

A Simple Procedure:

CREATE PROCEDURE PROC1

AS

BEGIN

PRINT 'MY FIRST PROCEDURE'

END

-Executing the above procedure:

EXEC PROC1 OR EXECUTE PROC1

A Procedure which accepts arguments:

ALTER PROCEDURE PROC2(@X INT, @Y INT)

AS

BEGIN

DECLARE @Z INT

SET @Z=@X+@Y

PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)

END

-Executing the above procedure in 2 ways:

  1. EXEC PROC2 100, 50
  2. EXEC PROC2 @X=100, @Y=50

A Procedure with Default Values:

CREATE PROCEDURE PROC3(@X INT = 100, @Y INT)

AS

BEGIN

DECLARE @Z INT

SET @Z=@X+@Y

PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)

END

-Executing the above procedure:

  1. EXEC PROC3 200, 25
  2. EXEC PROC3 @X=200, @Y=25
  3. EXEC PROC3 @X=DEFAULT, @Y=25
  4. EXEC PROC3 @Y=25

-In the 3rd and 4th case it uses the default value of 100 to the varibale X which has been given while creating the procedure.

A Procedure with OUTPUT Parameter:

CREATE PROCEDURE PROC4(@X INT, @Y INT, @Z INT OUTPUT)

AS

BEGIN

SET @Z=@X+@Y

END

- Executing the above procedure:

DECLARE @A INT

EXECUTE PROC4 500, 250, @A OUTPUT

PRINT @A

-A Procedure for Inserting values into the Emp Table:

CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)

As

Begin

INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)

End

- Executing the above Procedure:

EXEC Insert_Emp 1016, 'Sudhakar', 2500, 10

-A Procedure for Inserting values into the Emp Table but with Validations:

-This is same as the previous one but with the following validations present in it:

-Empno cannot be NULL value.

-Empno cannot be duplicated.

-Salary cannot be less than 2500.

-Deptno should be present in the Dept Table.

CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)

As

Begin

IF @Empno IS NULL

Begin

Print 'Empno cannot be NULL'

Return

End

IF Exists(SELECT * FROM Emp WHERE Empno=@Empno)

Begin

Print 'Empno cannot be Duplicated'

Return

End

IF @Sal<2500 Begin

Print 'Salary cannot be less than 2500'

Return

End

IF Not Exists(SELECT * FROM Dept WHERE Deptno=@Deptno)

Begin

Print 'Deptno not found in the Dept Table'

Return

End

INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)

End

-A Procedure which takes the Empno and returns the Provident Fund and Professional Tax at 12% and 5% respectively on the Salary.

CREATE PROCEDURE Deductions(@Empno int, @PF money OUTPUT, @PT money OUTPUT)

As

Begin

Declare @Sal Money

SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno

SET @PF=@Sal * 0.12

SET @PT=@Sal * 0.05

End

-Executing the above Procedure:

Declare @VPF money, @VPT money

EXEC Deductions 1005, @VPF OUTPUT, @VPT OUTPUT

Print @VPF

Print @VPT

-A Procedure which takes the Empno and prints the Net Salary of the Employee.

CREATE PROCEDURE Net_Sal(@Empno int)

As

Begin

Declare @VSal money, @NSal money, @VPF money, @VPT money

EXEC Deductions @Empno, @VPF OUTPUT, @VPT OUTPUT

SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno

SET @NSal = @VSal – @VPF – @VPT

Print 'Net Salary of the Employee is: ' + Cast(@NSal as Varchar)

End

-Executing the above Procedure:

EXEC Net_Sal 1005

-A Procedure which will Insert values into the Dept table by generating a unique Deptno.

CREATE PROCEDURE Insert_Dept(@Dname varchar(50), @Loc varchar(50))

As

Begin

Declare @Deptno int

Select @Deptno = ISNULL(MAX(Deptno), 0) + 10 FROM Dept

INSERT INTO Dept Values (@Deptno, @Dname, @Loc)

End

-Executing the above Procedure:

EXEC Insert_Dept 'Research', 'Hyderabad'

-A Procedure which is used from transferring amount from one account to the other within the Bank table:

CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)

As

Begin

UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID

UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID

End

-Executing the above Procedure:

EXEC Funds_Transfer 101, 102, 500

-          In the above case if the SrcID or DestID are not present in the table then  it will deduct the amount from the other or add the amount from the other to avoid this we need to use transaction management.

-          To manage the transaction first we need to identify which statement is executed and which failed for this we use the function @@ROWCOUNT.

-          @@ROWCOUNT returns the number of rows affected by the last statement.

-Managing Transactions in the Procedure:

CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)

As

Begin

Declare @Count1 int, @Count2 int

Begin Transaction

UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID

Set @Count1=@@ROWCOUNT

UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID

Set @Count2=@@ROWCOUNT]

IF @COUNT1=@COUNT2

Begin

COMMIT

PRINT 'TRANSACTION COMMITED'

End

ELSE

Begin

ROLLBACK

PRINT 'TRANSACTION ROLLED BACK'

End

End

Handling Errors in Procedures:

-          In SQL Server when a error occurs, the statement that caused the error is terminated, but the execution of the stored procedure or batch continues.

-          When stored procedures and batches are executed within the scope of a TRY block, batch abort errors can be handled by the TRY…CATCH construct.

-          Errors in Transact-SQL code can be processed using a TRY…CATCH construct similar to the exception-handling features of the languages.

-          A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block.

-          When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.

-          After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement.

-          If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.

-          Transact-SQL statements in the TRY block following the statement that generates an error will not get executed.

-          If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement.

-          If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.

-          A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement.

-          One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.

-          A CATCH block must follow a TRY block immediately.

-          A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement.

-          In Transact-SQL, each TRY block is associated with only one CATCH block.

-A Procedure which can cause Error:

CREATE PROCEDURE Div(@X int, @Y int)

As

Begin

Declare @Z int

SET @Z=0

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

END

-Executing the above procedure:

EXEC DIVX 100, 20

EXEC DIVX 100, 0

-The first execution will print the result of 5 but the second time execution will raise an error because we cannot divide a number by zero, in this case still it will try to print the result as 0, because even if the error is encountered it will not stop the execution of the program, if we want to stop the execution of the program when an error raises the code has to be written in the following way:

CREATE PROCEDURE Div(@X int, @Y int)

As

Begin

Begin Try

Declare @Z INT

SET @Z=0

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

End Try

Begin Catch

Print Error_Message()

End Catch

End

-Executing the above procedure:

EXEC DIVX 100, 20

EXEC DIVX 100, 0

-          Every error has 4 properties to it, they are:

  • Msg id
  • Msg str
  • Severity
  • State

For Example try the following statement:

Print 100/0

-This will display the following error message:

Msg 8134, Level 16, State 1,

Divide by zero error encountered.

-In this the Msg id is 8134, Msg str is "Divide by zero error encountered", Severity Level is 16 and State is 1.

Msg id: ID of the message, which is unique across server. Message IDs less than 50000 are system messages.

Msg str: Error message that has to be displayed when the error raises.

Severity Level: Severity level that is associated with the error. Severity levels can range between 0 and 25. Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

State: Is an arbitrary integer from 1 through 127. If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

Raising Errors Manually: We can also raise errors manually at some required situations. It is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. For raising an error manually we use the Raiserror Statement.

It generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

RAISERROR can return either:

  • A user-defined error message that has been created using the sp_addmessage system stored procedure.
  • A message string specified in the RAISERROR statement.

RAISERROR can also:

  • Assign a specific error number, severity, and state.
  • Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
  • Substitute argument values into the message text, much like the C language printf function.

Syntax: RAISERROR ( msg_id | msg_str | @local_variable, severity, state

[, argument [ ,...n ] ] )

[ WITH option [ ,...n ] ]

msg_id: Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

msg_str: Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. The parameters that can be used in msg_str are:

d or i                            Signed Integer

s                                   String

u                                  Unsigned Integer

These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to Transact-SQL data types, while the specifications used in printf map to C language data types.

@local_variable: Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar.

Severity: Is the user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.

Any user can specify severity levels from 0 through 18. Members of the sysadmin fixed server role permissions can only specify severity levels from 19 through 25, for which the WITH LOG option is required.

State: Is an arbitrary integer from 1 through 127. A negative value for state defaults to 1. The value 0 or values larger than 127 generate an error.

Argument: Are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

Option: Is a custom option for the error and can be one of the values in the following table.

  1. LOG: Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role can specify WITH LOG.
  1. NOWAIT: Sends messages immediately to the client.
  1. SETERROR: Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block.

The error is returned to the caller if RAISERROR is run:

  • Outside the scope of any TRY block.
  • With a severity of 10 or lower in a TRY block.
  • With a severity of 20 or higher that terminates the database connection.

CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.

-A procedure to divide 2 numbers and will raise an error when the divisor is 1.

CREATE PROCEDURE Divx(@X int, @Y int)

As

Begin

Declare @Z INT

SET @Z=0

IF @Y=1

RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

End

-Executing the above procedure:

EXEC DIVX 100, 20

EXEC DIVX 100, 1

-In the above case the RAISERROR statement raises the error but still next statements get executed. So if we want to stop the execution on the same line the code has to be enclosed with in the Try and Catch blocks.

CREATE PROCEDURE Divx(@X int, @Y int)

As

Begin

Begin Try

Declare @Z INT

SET @Z=0

IF @Y=1

RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

End Try

Begin Catch

PRINT ERROR_MESSAGE()

End Catch

End

-Executing the above procedure:

EXEC DIVX 100, 1

-In the above case when the error is raised the control transfers to the catch block and prints the error message associated with the error.

-If we want to customize the error message with formatting we can use the Raiserror statement as following:

RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y)

-In this case substituting the value of variable @X at the first % d location and the @y at second % d location it will generate the error message.

-We can also use the "WITH LOG" option at the end of the string to write the error message into the SQL Server Log File as following:

RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y) WITH LOG

-After running the procedure which will generate the error go and verify under the following location in the Object Explorer of the Management Studio i.e. under the Management node, SQL Server logs node, Current node click on it where we find the error message.

Pre-defined Errors: All the predefined error list of sql server can be found in the SYS.Messages Catalog View. Query on the database with the following statement where we can view the list of predefined errors:

-SELECT * FROM SYS.MESSAGES

-This will display the list of errors with their error_id, severity level, error_msg and language_id.

-We can also insert our own user defined error messages into it and use them when required, but because this is a System Catalog View we cannot directly insert records into it, so SQL Server provides a predefined Procedure SP_AddMessage which when called will insert the record into the Catalog View.

SP_AddMessage: Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages stored using sp_addmessage can be viewed using the sys.messages catalog view.

Syntax: sp_addmessage [ @msgnum = ] msg_id ,

[ @severity = ] severity ,

[ @msgtext = ] 'msg'

[ , [ @lang = ] 'language' ]

[ , [ @with_log = ] 'with_log' ]

[ , [ @replace = ] 'replace' ]

[ @msgnum = ] msg_id: Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language.

[ @severity = ] severity: Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25.

[ @msgtext = ] 'msg': Is the text of the error message. msg is nvarchar(255) with a default of NULL.

[ @lang = ] 'language': Is the language for this message. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.

[ @with_log = ] { 'TRUE' | 'FALSE' ] }: Is whether the message is to be written to the Windows application log when it occurs. The @with_log is varchar(5)with a default of FALSE. If TRUE, the error will be written in to the Windows application log. If a message is written to the Windows application log, it is also written to the Database Engine error log file.

[ @replace = ] 'replace': If specified as the string replace, an existing error message is overwritten with new message text and severity level. @replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.

EXEC sp_addmessage 50001, 16, 'Cannot Divide the Number by One'

-The above statement will insert a record into the SYS.Messages System Catalog after it was inserted we can use the raiseerror statement as following in our previous procedure:

Raiserror(50001, 16, 1)

-So when the error is raised the corresponding error message is picked out from the Catalog View and displayed to the user.

-Add Procedure, which will delete a record from the dept table for the given deptno and will raise an error if the deptno has any child records in the emp table.

CREATE PROCEDURE Delete_Dept(@Deptno int)

As

Begin

IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)

Raiserror('Child Records Found', 15, 1)

ELSE

DELETE FROM Dept WHERE Deptno=@Deptno

End

-After creating a Procedure at any time if we want to view the content of it write the following statement:

SP_HELPTEXT <procedure_name>

SP_HELPTEXT Delete_Dept

Creating a Procedure using With Encryption Option:

CREATE PROCEDURE Delete_Dept(@Deptno int)

WITH ENCRYPTION

As

Begin

IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)

Raiserror('Child Records Found', 15, 1)

ELSE

DELETE FROM Dept WHERE Deptno=@Deptno

End

-If the Procedure is created by using the With Encryption Option even if we use the SP_HELPTEXT also we cannot view the content of it. Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. The parameters that can be used in msg_str are:

d or i                            Signed Integer

s                                   String

u                                  Unsigned Integer

These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to Transact-SQL data types, while the specifications used in printf map to C language data types.

@local_variable: Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar.

Severity: Is the user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.

Any user can specify severity levels from 0 through 18. Members of the sysadmin fixed server role permissions can only specify severity levels from 19 through 25, for which the WITH LOG option is required.

State: Is an arbitrary integer from 1 through 127. A negative value for state defaults to 1. The value 0 or values larger than 127 generate an error.

Argument: Are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

Option: Is a custom option for the error and can be one of the values in the following table.

  1. LOG: Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role can specify WITH LOG.
  1. NOWAIT: Sends messages immediately to the client.
  1. SETERROR: Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block.

The error is returned to the caller if RAISERROR is run:

  • Outside the scope of any TRY block.
  • With a severity of 10 or lower in a TRY block.
  • With a severity of 20 or higher that terminates the database connection.

CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.

-A procedure to divide 2 numbers and will raise an error when the divisor is 1.

CREATE PROCEDURE Divx(@X int, @Y int)

As

Begin

Declare @Z INT

SET @Z=0

IF @Y=1

RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

End

-Executing the above procedure:

EXEC DIVX 100, 20

EXEC DIVX 100, 1

-In the above case the RAISERROR statement raises the error but still next statements get executed. So if we want to stop the execution on the same line the code has to be enclosed with in the Try and Catch blocks.

CREATE PROCEDURE Divx(@X int, @Y int)

As

Begin

Begin Try

Declare @Z INT

SET @Z=0

IF @Y=1

RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)

SET @Z=@X/@Y

PRINT 'The Output is: ' + Cast(@Z as varchar)

End Try

Begin Catch

PRINT ERROR_MESSAGE()

End Catch

End

-Executing the above procedure:

EXEC DIVX 100, 1

-In the above case when the error is raised the control transfers to the catch block and prints the error message associated with the error.

-If we want to customize the error message with formatting we can use the Raiserror statement as following:

RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y)

-In this case substituting the value of variable @X at the first % d location and the @y at second % d location it will generate the error message.

-We can also use the "WITH LOG" option at the end of the string to write the error message into the SQL Server Log File as following:

RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y) WITH LOG

-After running the procedure which will generate the error go and verify under the following location in the Object Explorer of the Management Studio i.e. under the Management node, SQL Server logs node, Current node click on it where we find the error message.

Pre-defined Errors: All the predefined error list of sql server can be found in the SYS.Messages Catalog View. Query on the database with the following statement where we can view the list of predefined errors:

-SELECT * FROM SYS.MESSAGES

-This will display the list of errors with their error_id, severity level, error_msg and language_id.

-We can also insert our own user defined error messages into it and use them when required, but because this is a System Catalog View we cannot directly insert records into it, so SQL Server provides a predefined Procedure SP_AddMessage which when called will insert the record into the Catalog View.

SP_AddMessage: Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages stored using sp_addmessage can be viewed using the sys.messages catalog view.

Syntax: sp_addmessage [ @msgnum = ] msg_id ,

[ @severity = ] severity ,

[ @msgtext = ] 'msg'

[ , [ @lang = ] 'language' ]

[ , [ @with_log = ] 'with_log' ]

[ , [ @replace = ] 'replace' ]

[ @msgnum = ] msg_id: Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language.

[ @severity = ] severity: Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25.

[ @msgtext = ] 'msg': Is the text of the error message. msg is nvarchar(255) with a default of NULL.

[ @lang = ] 'language': Is the language for this message. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.

[ @with_log = ] { 'TRUE' | 'FALSE' ] }: Is whether the message is to be written to the Windows application log when it occurs. The @with_log is varchar(5)with a default of FALSE. If TRUE, the error will be written in to the Windows application log. If a message is written to the Windows application log, it is also written to the Database Engine error log file.

[ @replace = ] 'replace': If specified as the string replace, an existing error message is overwritten with new message text and severity level. @replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.

EXEC sp_addmessage 50001, 16, 'Cannot Divide the Number by One'

-The above statement will insert a record into the SYS.Messages System Catalog after it was inserted we can use the raiseerror statement as following in our previous procedure:

Raiserror(50001, 16, 1)

-So when the error is raised the corresponding error message is picked out from the Catalog View and displayed to the user.

-Add Procedure, which will delete a record from the dept table for the given deptno and will raise an error if the deptno has any child records in the emp table.

CREATE PROCEDURE Delete_Dept(@Deptno int)

As

Begin

IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)

Raiserror('Child Records Found', 15, 1)

ELSE

DELETE FROM Dept WHERE Deptno=@Deptno

End

-After creating a Procedure at any time if we want to view the content of it write the following statement:

SP_HELPTEXT <procedure_name>

SP_HELPTEXT Delete_Dept

Creating a Procedure using With Encryption Option:

CREATE PROCEDURE Delete_Dept(@Deptno int)

WITH ENCRYPTION

As

Begin

IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)

Raiserror('Child Records Found', 15, 1)

ELSE

DELETE FROM Dept WHERE Deptno=@Deptno

End

-If the Procedure is created by using the With Encryption Option even if we use the SP_HELPTEXT also we cannot view the content of it.