comma separated value select

ALTER PROCEDURE  [dbo].[GetCheckoutCheckedFile](@filenameurl as varchar(100),@Acls as nvarchar(50),@itemid as varchar(1000))
AS

declare @site as varchar(50)
set @site=@Acls
declare @t1 table
(
t1 int
)
declare

@ParsedList table
(
    OrderID int
)

BEGIN
    DECLARE @OrderID varchar(10), @Pos int

    SET @itemid = LTRIM(RTRIM(@itemid))+ ','
    SET @Pos = CHARINDEX(',', @itemid, 1)

    IF REPLACE(@itemid, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
                SET @OrderID = LTRIM(RTRIM(LEFT(@itemid, @Pos - 1)))
                IF @OrderID <> ''
                BEGIN
                        INSERT INTO @ParsedList (OrderID)
                        VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
                END
                SET @itemid = RIGHT(@itemid, LEN(@itemid) - @Pos)
                SET @Pos = CHARINDEX(',', @itemid, 1)

        END
/*select * from @ParsedList*/

/*select * from checkinoutdetails where itemid in(select * from @ParsedList)*/
    END
  
END

while charindex(',',@site) > 0
begin
insert into @t1 select substring(@site,1,(charindex(',',@site)-1))
SET @site = substring(@site,charindex(',',@site)+1,len(@site))
end
insert into @t1
select @site

if 1=(select top 1 * from  @t1)
select * from documents a,checkinoutdetails b where a.itemid=b.itemid and filenameurl=@filenameurl and b.itemid in (select * from @ParsedList)
else
select * from documents a,checkinoutdetails b where a.itemid=b.itemid and filenameurl=@filenameurl and  (Acls  in(select * from @t1)) and acls !='1' and b.itemid in (select * from @ParsedList)





create PROCEDURE  [dbo].[GetMyCheckoutCheckedFileByItemIds]
(

@itemid as varchar(1000),
@username as varchar(100)
)
AS

declare @ids as varchar(200)
set @ids=@itemid
declare @t1 table
(
t1 int
)
while charindex(',',@ids) > 0
begin
insert into @t1 select substring(@ids,1,(charindex(',',@ids)-1))
SET @ids = substring(@ids,charindex(',',@ids)+1,len(@ids))
end
insert into @t1
select @ids

select * from  documents a,checkinoutdetails b where a.itemid in
(select * from @t1 ) and b.itemid in
(select * from @t1 ) and b.emailid=@username and a.createdbyuser=@username

Working with hierarchical data in SQL Server databases

Working with hierarchical data in SQL Server databases

Note: Information & code samples from this article are tested on SQL Server 2005 RTM (Yukon) and found to be working. Will update the article in case of any compatibility issues.

In this article, I will show you a simple example that traverses a hierarchy and displays the output in indented format. I will also provide you with links and pointers for more advanced and in-depth information on processing hierarchies.

Representing data in the form of a hierarchy or a tree is a common requirement. The most common example I can think of, is an organizational chart that contains all the employees in a given organization and each employee is linked to his/her manager by his/her manager's ID. Since managers are also employees, their details also get stored in the same employees table.

A few more examples:

- Implementation of a permissions hierarchy.

- Implementation of a product catalog for an online store.

All the above examples need data to be stored in the form of hierarchies or trees (also loosely referred to as parent child relationships). But SQL Server is a relational database, not a hierarchical database. So, we have to store the data in normalized, relational tables, but come up with programming techniques to process this data in a hierarchical manner.

Unfortunately, there is no built-in support for processing hierarchies and tree structures in SQL Server's T-SQL implementation. (Oracle's PL/SQL implementation has a CONNECT BY syntax that makes it easier to work with hierarchical data. Let's hope we will see something similar in the next version of SQL Server, Yukon).

C programmers use recursive programming techniques for traversing tree structures. The same can be implemented in T-SQL using recursive stored procedure calls. I will demonstrate this in the following example.

Consider the employee table of an organization, that stores all the employee records. Each employee is linked to his/her manager by a manger ID. This table can be represented using the following CREATE TABLE statement:

CREATE TABLE dbo.Emp
(
 EmpID  int PRIMARY KEY,
 EmpName  varchar(30),
 MgrID  int FOREIGN KEY REFERENCES Emp(EmpID)
)
GO

Notice that, EmpID is decalred as a primary key, and the MgrID column is declared as a foreign key constraint, that references the EmpID column of the same table, that is, a self referencing table. This is so, because all employees and managers are stored in the same table.

Since EmpID is declared as a primary key, by default it will be implemented as a unique clustered index. Now, let's create a non-clustered index on MgrID column, to improve the query performance:

CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
GO

Let's populate the employee table with some data:

INSERT dbo.Emp SELECT 1, 'President', NULL
INSERT dbo.Emp SELECT 2, 'Vice President', 1
INSERT dbo.Emp SELECT 3, 'CEO', 2
INSERT dbo.Emp SELECT 4, 'CTO', 2
INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4
INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5
INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5
INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6
INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8
INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8
INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6
INSERT dbo.Emp SELECT 12, 'Tester 1', 11
INSERT dbo.Emp SELECT 13, 'Tester 2', 11
INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7
INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14
INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14
INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7
INSERT dbo.Emp SELECT 18, 'Tester 3', 17
INSERT dbo.Emp SELECT 19, 'Tester 4', 17
INSERT dbo.Emp SELECT 20, 'Tester 5', 17
GO

Notice that the MgrID of President is NULL, that means, he is the super boss and has nobody managing him. As you can see, rest of the employees are linked to their respective managers using the MgrID column.

Now, let's create a stored procedure, that traverses this employee hierarchy recursively and displays the employees in the form of an indented tree structure. The following stored procedure has an input parameter named Root, that takes the ID of the employee (equivalent to the ID of a node in a tree) and displays all the employees managed by him and his sub-ordinates.

CREATE PROC dbo.ShowHierarchy
(
 @Root int
)
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @EmpID int, @EmpName varchar(30)

 SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
 PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

 SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

 WHILE @EmpID IS NOT NULL
 BEGIN
  EXEC dbo.ShowHierarchy @EmpID
  SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)
 END
END
GO

While creating the above stored procedure, you will receive the following warning:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ShowHierarchy'. The stored procedure will still be created.

Nothing to worry about. It's a just a warning, as SQL Server is a little confused about the recursive stored procedures.

As you can see, the above stored procedure calls itself recursively. You should be aware of a limitation imposed by SQL Server though. A stored procedure can nest itself upto a maximum of 32 levels. If you exceed this limit, you will receive the following error:

Server: Msg 217, Level 16, State 1, Procedure sdss, Line 1
Maximum stored procedure nesting level exceeded (limit 32).

Now let's run this procedure by passing the IDs of different employees and look at the output:

--Complete hierarchy

EXEC dbo.ShowHierarchy 1
GO


---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
---------------------Tester 1
---------------------Tester 2
---------------Project Manager 2
------------------Team Leader 2
---------------------Software Engineer 3
---------------------Software Engineer 4
------------------Test Lead 2
---------------------Tester 3
---------------------Tester 4
---------------------Tester 5



--From Group Project Manager onwards

EXEC dbo.ShowHierarchy 5
GO


---Group Project Manager
------Project Manager 1
---------Team Leader 1
------------Software Engineer 1
------------Software Engineer 2
---------Test Lead 1
------------Tester 1
------------Tester 2
------Project Manager 2
---------Team Leader 2
------------Software Engineer 3
------------Software Engineer 4
---------Test Lead 2
------------Tester 3
------------Tester 4
------------Tester 5



--From Project Manager 1 onwards

EXEC dbo.ShowHierarchy 6
GO


---Project Manager 1
------Team Leader 1
---------Software Engineer 1
---------Software Engineer 2
------Test Lead 1
---------Tester 1
---------Tester 2

The above is just a simple example of traversing hierarchies. There's more to hierarchies than just traversing, that is, adding to, deleting from and modifying hierarchies. The following links deal with hierarchies in greater detail and propose different methodologies for managing hierarchies efficiently.

SQL Server recursion - Parent child relations

I'm sure that many of the experienced database developers in here already know about this technique, but for the rest of you I though it would be nice to have a tutorial on how you can query multi-level parent-child data, both one to many and many to many relationsships.

Examples of a one to many multi-level structure are for example organisations structures or a threaded discussion forum (like VBForums).

A many to many structure can be a Bill of Material (BOM). A BOM can have many items and an item can be part of many BOMs.

The problem with these kind of relations is how write a query that will search and return the whole tree for a given record, e.g. the whole subtree (or x levels) of a BOM or all replies to a post in a discussion forum.

I will start with an example where we look at an employee structure. An employee has one manager, and a manager can have many employees. We create a table Employee with Employee_Id, First_Name, Last_Name, and Manager_Id.

Code:

CREATE TABLE Employee (
 Employee_Id int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
 First_Name varchar(50) NOT NULL,
 Last_Name varchar(50) NOT NULL,
 Manager_Id int NULL constraint [FK_Manager_Employee] FOREIGN KEY
  (Manager_Id) REFERENCES Employee(Employee_Id)
) ON [PRIMARY]
GO
-- We also create an index on Manager_Id to speed things up a little bit.
CREATE NONCLUSTERED INDEX IX_Manager ON Employee
 (
 Manager_Id
 ) ON [PRIMARY]
GO

To make it even more understandable we will populate the table with "employees" from VBForums database section, and I hope nobody gets offended by this chart.



The following TSQL will insert this structure into the Employee table:

Code:

insert into Employee(First_Name, Last_Name, Manager_Id) values('si_the_geek','NA',NULL)
insert into Employee(First_Name, Last_Name, Manager_Id) values('mendhak','NA',1)
insert into Employee(First_Name, Last_Name, Manager_Id) values('szlamany','NA',1)
insert into Employee(First_Name, Last_Name, Manager_Id) values('vb_dba','NA',1)
insert into Employee(First_Name, Last_Name, Manager_Id) values('techgnome','NA',3)
insert into Employee(First_Name, Last_Name, Manager_Id) values('hack','NA',3)
insert into Employee(First_Name, Last_Name, Manager_Id) values('kaffenils','NA',5)
insert into Employee(First_Name, Last_Name, Manager_Id) values('dee-u','NA',5)

We have now the created the Employee table and populated it with test data. As you can see, the Employee structure consists of four levels.

The next step is to create a function or stored procedure that can take an Employee_Id as parameter and list all sub-levels in the employee hierarcy. This means that if we execute the query with Employee_Id=1 (si_the_geek), we will get all the records in the table returned.

The script that does the magic is here. Comments in the script explains how it works.

-- Update the first level's Path and Employee_Id_Path. Employee_Id_Path will be used to prevent the recursion from
-- going into an endless loop if for example si_the_geek is defined as both parent and child to mendhak.
update @tree set Path=str(TreeId,10,0) + '.', Employee_Id_Path=cast(Employee_Id as varchar(10)) + '\'

while @rowcount>0
begin
 -- Increase level by one.
 set @lvl=@lvl+1 

 -- This line inserts all records from the Employee table that has the previous level's
 -- employee_Id as Manager_Id. In other words, it inserts all manager's staff from the previous
 -- execution of the loop.
 insert into @tree(Employee_Id, lvl, ParentTreeId)
  select e.Employee_Id, @lvl, t.TreeId
  from Employee e inner join @tree t on e.Manager_Id=t.Employee_Id and t.lvl=@lvl-1

 -- Get number of rows affected by the previous insert. If no records were affected by the last
 -- statement, then we know that we have reached the bottom of the hierarcy.
 set @rowcount=@@ROWCOUNT
 
 -- The following SQL updates the newly inserted records' Path with the
 -- the new TreeId + previous levels TreeId. The path is used later to sort
 -- the result in a treeview look-a-like way. We also update Employee_Id_Path
 -- with the Employee_Id and previous level's Employee_Id_Path. The Employee_Id_Path
 -- is used to detect endless loops and therefore we only update Employee_Id_Path
 -- where parent Employee_Id_Path does not contain current Employee_Id. This is
 -- perhaps a little bit difficult to understand the first time you read the code.
 update t1 set t1.Path=t2.Path + str(t1.TreeId,10,0)+'.',
  t1.Employee_Id_Path=t2.Employee_Id_Path + cast(t1.Employee_Id as varchar(10))+'\'
  from @tree t1 inner join @tree t2 on t1.ParentTreeId=t2.TreeId
  where t1.lvl=@lvl and t2.Employee_Id_Path not like '%' + cast(t1.Employee_Id as varchar(10)) + '\%'
 
 -- This statement deletes andy records where Employee_Id_Path is NULL. In other
 -- words: We delete records that will cause an endless loop.
 delete from @tree where Employee_Id_Path is null
 
 -- We then get the number of records deleted...
 set @delcount=@@ROWCOUNT

 -- ... and substract this number from the records appended. If @rowcount=0
 -- then the WHILE loop will exit.
 set @rowcount=@rowcount-@delcount
end

-- Now we can choose to display the results in what way we want to. Path can, as we said before,
-- be used to sort the result in a treeview way.
select replicate(' | ',lvl)+cast(t.Employee_Id as varchar(10)) as tree_level,e.First_Name,lvl
from @tree t inner join Employee e on t.Employee_Id=e.Employee_Id order by Path


GO

 You can use this script in a stored procedure or a UDF. Using the script in a UDF will also give you the possibility to use the result as a recordset in other SELECT statements. Read here to see how the UDF header must be defined to act as a recordset.

Execute the script in QA and you will get the following result. As you can see, this is a treeview representation of the hierarcy displayed in the picture above.



This example demonstrates the basic principle, but you can make it much more advanced if you like to. We have used it to return a distinct list of all items in a BOM and the quantity of each item.

Enjoy, and feel free to add questions or comments.

Getting Last inserted Identity value in SQL server

In this article I would like to share my idea about getting Identity after a row was inserted in to the SQL Server 2005.
After inserting a row into the database which has primary key feild, most of the time we need the identity, We have three approches based on our reqiurements and situations.

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(‘TableName’)

All of the abouve three will get the identity value but in different approches.

The variable @@IDENTITY will return the last generated identity value produced on a connection, without based on the table that produced the value. While @@IDENTITY is limited to the current session, it is not limited to the current scope. This means that if we insert some record in Table1 which has a trigger on the insert and the trigger inserts a record in some other table2 then the @@IDENTITY will return the identity value inserted in Table2.

SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table1.

Use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.

Copy table

select * into employee1  from employee where id=5

select * into employee1  from employee

//CREATE NEW TABLE AND COPY ALL THE TABLE CONTENT

select * from employee1

drop table employee1

SELECT *

INTO employee1

FROM employee

WHERE 1=2

//ONLY COPY STRUCTURE

INSERT INTO employee1 (empname, address)

SELECT empname, address

FROM employee

WHERE id = 2

select * into <destination table> from <source table>

Example:

Select * into employee_backup from employee

 We can also select only a few columns into the destination table like below

select col1, col2, col3 into <destination table>

from <source table>

Example:

Select empId, empFirstName, empLastName, emgAge into employee_backup

from employee

Use this to copy only the structure of the source table.

select * into <destination table> from <source table> where 1 = 2

Example:

select * into employee_backup from employee where 1=2

Use this to copy a table across two database in the same Sql Server.

select * into <destination database.dbo.destination table>

from <source database.dbo.source table>

Example:

select * into Mydatabase2.dbo.employee_backup

from mydatabase1.dbo.employee

On Cascade

CREATE TABLE employee (emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY,
emp_fname CHAR(20) NOT NULL,
emp_lname CHAR(20) NOT NULL,
dept_no CHAR(4) NULL)

CREATE TABLE project (project_no CHAR(4) NOT NULL CONSTRAINT prim_pro PRIMARY KEY,
project_name CHAR(15) NOT NULL,
budget FLOAT NULL)

CREATE TABLE works_on1
(emp_no INTEGER NOT NULL,
project_no CHAR(4) NOT NULL,
job CHAR (15) NULL,
enter_date DATETIME NULL,

CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),

CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no) REFERENCES employee(emp_no) ON DELETE CASCADE,

CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADE)

-- The creation of the works_on1 table that uses the ON DELETE CASCADE and ON UPDATE CASCADE options

Difference between Delete and Truncate

Difference Between Delete and Truncate

  .Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

. Truncate table also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

. Truncate table is functionally identical to delete statement with no "where clause" both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

. Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

. In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

. If you want to retain the identity counter, use delete statement instead.

. If you want to remove table definition and its data, use the drop table statement.

. You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

. Truncate table may not be used on tables participating in an indexed view.

TRUNCATE

.TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

.You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

.TRUNCATE cannot be rolled back.

.TRUNCATE is DDL Command.

.TRUNCATE Resets identity of the table

DELETE

.DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

.DELETE Can be used with or without a WHERE clause

.DELETE Activates Triggers.

.DELETE can be rolled back.

.DELETE is DML Command.

.DELETE does not reset identity of the table

Truncate 

Resets identity of the table

DDL Command

Cannot be rolled back

Faster (Uses Fewer systems)

Where can't be used

removes the data by de-allocating the data pages

TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

Trigger can't be activated

Delete

does not reset identity of the table

DML Command

can be rolled back

Slower (Removes row one by one)

WHERE Condition can be used

records an entry in the transaction log for each deleted row

Retain Identity counter

Trigger can be activated

http://www.dotnetspark.com/qa/6-difference-between-delete-and-truncate.aspx

INSERT TABLE OF CONTENT OF ONE TABLE OF ONE DATABASE INTO ANOTHER TABLE OF ANOTHER DATABASE WHERE THE DESTINATION TABLE WILL BE AUTOMATICALLY CREATED

SELECT * INTO (DESTINATION) DATABASENAME.SCHEMANAME.TABLENAME FROM (SOURCE) DATABASENAME.SCHEMANAME.TABLENAME

EXAMPLE-SELECT * INTO EMP1.DBO.EMPLOYEE FROM EMP2.DBO.EMPLOYEE

INSERT TABLE OF CONTENT OF ONE TABLE OF ONE DATABASE INTO ANOTHER TABLE OF ANOTHER DATABASE

INSERT INTO (DESTINATION) DATABASENAME.SCHEMANAME.TABLENAME FROM  SELECT * FROM (SOURCE) DATABASENAME.SCHEMANAME.TABLENAME

 EXAMPLE ->INSERT INTO EMP1.DBO.EMPLOYEE FROM SELECT * FROM  EMP2.DBO.EMPLOYEE

Get the total structure of a table

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Tablename'

Maximum id of a table

select max(columnname) from tablename;

No of rows of a table

select count(*) from table name

Find the default values of the columns and the column names

(SELECT column_name,column_default
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table name')

Find the no of column of a table.

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TableName'

Sql Schema

What is Schema in SQL Server 2005? Explain its properties with example?

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement.

Properties

• Ownership of schemas and schema-scoped securables is transferable.
• Objects can be moved between schemas
• A single schema can contain objects owned by multiple database users.
• Multiple database users can share a single default schema.
• Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.
• A schema can be owned by any database principal. This includes roles and application roles.
• A database user can be dropped without dropping objects in a corresponding schema.

Create database SQL2k5

Use SQL2k5

– Created Schema Employee –

Create Schema Employee

– Created table in Employee schema –

Create Table Employee.EmpInfo

(

EmpNo int Primary Key identity(1,1),

EmpName varchar(20)

)

– data insertion –

Insert Into Employee.Empinfo Values(‘Jshah-3′)

– Data Selection –

Select * From Employee.Empinfo

– Created another schema HR –

Create Schema HR

– Transfer Objects between Schemas –

ALTER SCHEMA HR

TRANSFER Employee.Empinfo

– Assigning Permission to Schema –

GRANT SELECT ON SCHEMA::HR TO Jshah

Sql transaction

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.
Users can group two or more Transact-SQL statements into a single transaction using the following statements:

• Begin Transaction
• Rollback Transaction
• Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.
Here is an example of a transaction :

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.


http://www.codeproject.com/KB/database/sqlservertransactions.aspx

Error Handling in sql

Error Handling

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client�s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of @@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after any INSERT, UPDATE, or DELETE statement.

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), 
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

COMMIT TRAN

RETURN 0

http://www.sommarskog.se/error-handling-I.html

Trigger

create trigger trg on employee
for update
as
if Update(empname)
print 'Emp Name Updated'

update employee set empname='pkm' where id=1
o/p
Emp Name Updated

(1 row(s) affected)

If in query

if (select count(*) from emp)>0
select * from emp

Database Details

sSQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8,060	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per nonwide table	1,024	1,024
Columns per wide table	30,000	30,000
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4096	4096
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	999	999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP	32	32
Number of grouping sets generated by operators in the GROUP BY clause	4,096	4,096
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	30,000	30,000
Tables per SELECT statement	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096
User connections	32,767	32,767
XML indexes http://msdn.microsoft.com/en-us/library/ms143432.aspx	249	249