Wednesday, September 24, 2014

How to use OUTPUT Clause in MS SQL / What is the use of OUTPUT Clause?

Today we will learn some SQL. Have you ever used OUTPUT clause in MS SQL? What exactly us the use of OUTPUT clause in MS SQL? What is the advantage of using OUTPUT clause in SQL? All these questions are answered in this article.

What is OUTPUT clause in SQL?

OUTPUT clause gives you the information about each row impacted by running INSERT or UPDATE or DELETE or MERGE SQL statement. Normally in applications we use this information to return to the user in a meaningful manner to show the result of submitting the data.

What you can do is, you may save this information in a temporary table or in a permanent table itself for future use. You can use this information for auditing purpose also because it has got complete information about the sol statement which you executed row by row.

The main disadvantage of this OUTPUT clause is it returns the same information even if your SQL execution throws error or it is rolled back. So you need to make sure that such information should not be used for any auditing purpose or to pass the information to client.

Now lets see what kind of information we gets when we use OUTPUT clause.

First we will create a sample table.

CREATE TABLE [dbo].[tblBill](
    [BillDate] [datetime] NULL,
    [Expense] [numeric](18, 0) NULL,
    [travel] [numeric](18, 0) NULL
)
GO

Lets query the table and see the values we have inserted in this table.

image

Now we will delete one row with the OUTPUT clause and see the result and also the syntax to use output clause.

  delete from [DemoAzure].[dbo].[tblBill] 
  OUTPUT DELETED.*
  where travel = 150

As you can see above OUTPUT clause should be before where clause.

Once you execute this statement it will return the complete row which got deleted like a select statement. This is the reason I mentioned earlier that we can directly insert this values into a table for future use or for auditing purpose.

Lets see the result set now.

image

Lets see a sample code to insert the value in a table variable.

DECLARE @tblBillVar table( 
    [BillDate] [datetime] NULL,
    [Expense] [numeric](18, 0) NULL,
    [travel] [numeric](18, 0) NULL);
    
INSERT INTO [dbo].[tblBill] ([BillDate],[Expense],[travel]) 
OUTPUT INSERTED.[BillDate], INSERTED.[Expense], INSERTED.[travel]
        INTO @tblBillVar
VALUES ('2015-01-06 00:00:00.000',875,475)
 
select * from @tblBillVar

Once you execute above query you will find that the inserted values will be displayed in the query analyzer.

image

Similar to table variable you can have your own table as well.

I hope you are now clear about the OUTPUT clause in MS SQL.

Wednesday, September 3, 2014

GROUPING SETS in MS SQL

I have been doing some reporting work today. In that report I have to show some trending. In fact I have to show average salary based on the city and department. I was thinking what will be the easiest option to display in that manner, only thing is I don’t want to write huge SQL query to show this data.  Nothing to worry now, you have an option in MS SQL that is nothing by grouping set.
Today we will see how we can use GROUPING SET in SQL query and when exactly we can use this query.
First we will create a table with the name tblStatistics.