Search This Blog

Loading...

Tuesday, April 17, 2012

Looping through list in SQL


If we have a comma separated list and the requirement is to loop through the list of items one by one or to pass these items to a stored procedure. The easiest and simple way to do this is given below with coments.

--comma separated list
Declare @S varchar(20)
set @S ='abc,def,ghi,jkl' 


--loop through each item
while len(@S) > 0
Begin

--get the first item form the list
declare @S1 varchar(20)
set @S1 = left(@S, charindex(',' , @S+',')-1)

--Print it
Print @S1

--Delete the item from the list
set @S = Stuff(@S,1,charindex(',',@S+','),'')

End


Wednesday, April 11, 2012

TRANSACTION and Error Handling


Transactions are very useful when you are not sure if there could be a error or not. And if there is a error, the set of sql statements need not be executed or needs to be rolled back.In this case we can TRANSACTIONS that help us to ROLLBACK if there was any error while executing the sql statements.

The below example also shows simple error handling. If there is any error while updating the table then control is passed on to the CATCH block and the TRANSACTION is rolled back.

USE [PUBS]
GO

DECLARE @iError int

BEGIN TRANSACTION

    UPDATE table
    SET column = 'value'
    WHERE column = 'condtion'
   
    select @iError = @@ERROR
    IF (@iError <> 0) GOTO CATCH
   
COMMIT TRANSACTION

CATCH:
IF(@iError <> 0)
BEGIN
    PRINT 'Error message'
    ROLLBACK TRANSACTION
END


Tuesday, March 27, 2012

DELETE and TRUNCATE can be rolled back


As we all know, DELETE can be rolled be back even if it is committed, using the log file and some third party to tools.

Truncate can also be rolled back, if it is within a TRANSACTION and if the session is not closed.

Try the below code, first create a table, then insert some data into it. Then run these set of lines. You should get some data before truncate is run, after truncate there shouldn't be no data and after rollback, if the original data is returned then TRUNCATE CAN be rolled back.

NOTE: I am running this code in SQL SERVER 2008


BEGIN TRANSACTION

SELECT * FROM table1

TRUNCATE TABLE table1

SELECT * FROM table1

ROLLBACK

SELECT * FROM table1

Thursday, March 22, 2012

IDENTITY_INSERT

If you want to insert a specific value into a identity column of a sql server table then here is how you can do it.

SET IDENTITY_INSERT ON

And then insert the identity value into the table. If a table already has this property set to ON in the same session then SQL will throw out the error indicating which table is it set.

After inserting the value set it OFF by executing

SET IDENTITY_INSERT OFF

Wednesday, March 21, 2012

Types of Triggers and Creating Triggers


In this blog let us see how many types of trigger we can find and how to create them. Lets not waste any time then.

There are two kinds of triggers.
-After Triggers
-Instead of Triggers

After Triggers are of 3 types
-After Insert
-After Update
-After Delete

Instead of Triggers are of 3 types again.
-Instead of Insert
-Instead of Update
-Instead of Delete

Lets see an example in each category by creating the triggers and executing them

First, create a simple table with two columns.

CREATE TABLE [dbo].[TestTable](
    [EmpId] [varchar](50) NULL,
    [Name] [varchar](255) NULL
)


Create another table where the data will be inserted by the trigger.

CREATE TABLE [dbo].[AFTERINSERT](
    [EmpId] [varchar](50) NULL,
    [Name] [varchar](255) NULL
)


Insert some data into the TestTable.

INSERT INTO TESTTABLE
VALUES('01','ABC')

INSERT INTO TESTTABLE
VALUES('02','BCD')


Now create the below trigger that will insert the data into AfterInsert table and also print the data that has been inserted into TestTable

InsertTrigger is the name of the trigger and since we are creating trigger on TestTable for and insert we mention that in the trigger which is clearly readable.

In the body of the trigger, we create two variables to hold the data from INSERTED table. This is not a physical table but a logical one that holds the just inserted data.

And that data is inserted into the AfterInsert table.
 

CREATE TRIGGER INSERTTRIGGER ON TESTTABLE
FOR INSERT

AS

DECLARE @EMPID VARCHAR(255)
DECLARE @NAME VARCHAR(255)

SET @EMPID = I.EMPID FROM INSERTED I
SET @NAME = I.NAME FROM INSERTED

--PRINT THE ID AND THE NAME
PRINT @EMPID + ' ' + @NAME

--INSERT INTO AFTERINSERT TABLE
INSERT INTO AFTERINSERT
VALUES(@EMPID,@NAME)

GO

Now try inserting data into the TestTable by using this line

Insert into TestTable values('03', 'CDE')

You can create similar triggers for Update and Delete as well. For Update, there is no logical table, but have to use Inserted. For Delete, there is a table called Deleted. Use Deleted instead of Inserted in the above trigger for deletion.


Now lets create a trigger for Instead Of Delete. This can be used to intercept any modifications to the table like insert, update or delete.

Below example is for Instead of delete.

As explained before, since we are checking on TestTable, we have to mention the table name and Instead Of delete.
Most of the body is same except that, we are checking if emp id is '01'. If yes, then prints the message and rollsback. if not deletes it.

Create the below trigger and try to delete the emp id '01'.

CREATE TRIGGER INSTDDELETE ON TESTTABLE
INSTEAD OF DELETE

AS

DECLARE @EMPID VARCHAR(255)
DECLARE @NAME VARCHAR(255)

SET @EMPID = I.EMPID FROM DELETED I
SET @NAME = I.NAME FROM DELETED I

IF @EMPID = '01'
    BEGIN
        --PRINT THE ID AND THE NAME
        PRINT @EMPID + ' ' + @NAME + ' CANNOT BE DELETED'
        ROLLBACK
    END

GO

Similarly we can create Instead of triggers for Update and Insert as well. Instead of using Deleted table use Inserted table.

Friday, March 16, 2012

Debug.writeline

One of the class in .Net that is very useful to log messages or track the sequence of execution is to use 'System.Diagnostics.Debug' class. The Debug.Writeline("Message") writes the message to the output window while debugging. If you want this message to be written to a file then you need to have listener that listens to these messages which is better understood by the below example of code.

Place this code in a button click of windows application. This code is applicable only for vb.net.

    Dim Tr As TextWriterTraceListener
    Tr = New TextWriterTraceListener(System.IO.File.CreateText("Output.txt"))
    Debug.Listeners.Add(Tr)
    Debug.WriteLine("Test Message")
    Tr.Flush()
    Tr.Close()
    Tr.Dispose()


Declares a new TexWriterTraceListener that will create a new text file. Add this listener to the Debug class and start writing the messages. Then flush the buffer of listener and close and dispose the trace. The output.txt file should be placed in the debug folder of your application.

You can also use the Debug class to write the message on conditional basis. For example use:

Dim quantity as integer = 51
Debug.WritelineIf(quantity > 50, "This message will appear")
Debug.WritelineIf(quantity < 50, "This message will NOT appear")

There is one more method called 'Assert' that will display the message only if the evaluated condition returns false. For example:

Dim quantity as integer = 51
Debug.Assert(quantity > 50 "This message will not appear") 
Debug.Assert(quantity < 50 "This message will appear since the quantity < 50 is false")

For better readability, use Debug.Indent() method to indent subsequent messages. You can also use Debug.UnIndent() method to UnIndent subsequent messages.

Debug.Write() appends the message to the same line, while Debug.Writeline() writes the message to a new line.

At last, you can also use Trace.Writeline instead of Debug.Writeline. Both classes send messages to all the listeners in the debug mode. However, in release mode, only Trace.Writeline works.


Wednesday, March 14, 2012

Copy one database to another database

There are several ways to do this, below are two options:

Option 1
- Right click on the database you want to copy
- Choose 'Tasks' > 'Generate scripts'
- 'Select specific database objects'
- Check 'Tables'
- Mark 'Save to new query window'
- Click 'Advanced'
- Set 'Types of data to script' to 'Schema and data'
- Next, Next



You can now run the generated query on the new database.

Option 2
- Right click on the database you want to copy
- 'Tasks' > 'Export Data'
- Next, Next 

- Choose the database to copy the tables to
- Mark 'Copy data from one or more tables or views'
- Choose the tables you want to copy
- Finish