Update Statement with Custom Requirement

Hi Guys! hope you all doing great! Today I came up with a post on an interview question based on EMPLOYEE Table.

Task: Update Salaries of the Employees as per below requirement.

DEPTNO	INCREMENT
10	100
20	200
30	300
40	400
X0	X00

We can notice there is a correlation between DEPTNO and INCREMENT. For DEPTNO 10, its 100, DEPTNO 20, its 200, and so on.......for DEPTNO X0 its X00.

The structure of EMPLOYEE table is as below:

Input: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

Solution: Using WHILE Loop

DECLARE @INCREMENT INT = 100, @DEPTNO INT

DECLARE @TV TABLE (DEPTNO INT, FLAG BIT)

INSERT INTO @TV

SELECT DISTINCT DEPTNO, FLAG = 0 FROM EMPLOYEE

SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

WHILE (@DEPTNO IS NOT NULL)

BEGIN

            UPDATE EMPLOYEE

            SET SAL = SAL + @INCREMENT

            WHERE DEPTNO = @DEPTNO

            UPDATE @TV

            SET FLAG = 1

            WHERE DEPTNO = @DEPTNO AND FLAG = 0

            SET @INCREMENT = @INCREMENT + 100

            SET @DEPTNO = (SELECT TOP 1 DEPTNO FROM @TV WHERE FLAG = 0)

END

Output: SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMPLOYEE;

Conclusion: Compare both the screen shots and note that, the salaries are updated as per the requirement table mentioned on the top of this post. Hope this helps! Happy TSQL'ing ;)

Advanced T-SQL Coding Example

Here is a T-SQL task that needs a non-traditional (I repeat, Non Traditional.) way of coding. It should reach the expectations of Senior T-SQL Coding Standards and obviously highly tuned for performance.

Input Data: Here is the sample input data of Employees.

Output Needed: The expected output is as shown in figure below.

You guessed it right! Both detail and aggregate level data in a single output. 

Let me add, "the objective of this post here is about discussing the standard of T-SQL coding we use to achieve the task".

Traditional Way:

The below code is what comes to ones mind to achieve the task in a traditional way.

SELECT A.EMPNO, A.ENAME, A.SAL, B.SumSalDeptWise, C.SumSalWholeOrg

FROM EMP A

INNER JOIN

            (SELECT DEPTNO, SUM(SAL) AS SumSalDeptWise

                        FROM EMP

                        GROUP BY DEPTNO) B

            ON A.DEPTNO = B.DEPTNO

CROSS JOIN

            (SELECT SUM(SAL) AS SumSalWholeOrg FROM EMP) C

Now let's see the Non-Traditional and Highly Tuned Version of Code.

Non Traditional Way: (The future T-SQL Code)

SELECT

            EMPNO, ENAME, SAL,

            SUM(SAL) OVER (PARTITION BY DEPTNO) AS SumSalDeptWise,

            SUM(SAL) OVER () AS SumSalWholeOrg

FROM EMP;

Conclusion: Found the difference? The future of T-SQL coding is changing with newer versions of SQL Server. Hope someone gets benefitted with this post ! Happy TSQL'ing :)

Multiplication Table Using WHILE Loop

Spending sometime for T-SQL on weekends made me write a stored procedure that returns Multiplication Table for any given number.

Stored Procedure Code:

CREATE PROCEDURE dbo.usp_MultiplicationTable

@N INT

AS

BEGIN

SET NOCOUNT ON;

            DECLARE @I INT = @N, @J INT = 1

            DECLARE @TV TABLE (RESULT VARCHAR(20))

            WHILE (@J <= 10)

            BEGIN

                        INSERT INTO @TV

                        SELECT CAST(@I AS VARCHAR) + CHAR(32) + '*' + CHAR(32) +

                        CAST(@J AS VARCHAR) + CHAR(32) + '=' + CHAR(32) + CAST(@I * @J AS VARCHAR)

                       

                        SET @J = @J + 1

            END

            SELECT * FROM @TV;

END

Execution Code:

On passing '2' as the input value to the stored procedure, it returned below output:

Hope this helps someone!

Conclusion: T-SQL is like a girl friend. You get close to it, it loves back. You go away, it starts hating you. So Happy TSQLing ;)

EOMONTH - New Function in SQL Server 2012

EOMONTH: Returns the last day of the month for the specified date. Also picks up previous or next month's last day of month using an optional argument. So no more expressions with nested date functions :)

Syntax: EOMONTH(<DateColumn>, <month_to_add>)

Previously, we used to run expressions like below for Last Day Of Previous Month, Last Day of Current Month and Last Day of Next Month.

Code:

SELECT

[Last Day of Previous Month] = CONVERT(VARCHAR(10), DATEADD(DD,-(DAY(GETDATE())),GETDATE()), 101),

[Last Day of Current Month] = CONVERT(VARCHAR(10), DATEADD(DD,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())), 101),

[Last Day of Next Month] = CONVERT(VARCHAR(10), DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))), DATEADD(MONTH, 2, GETDATE())), 101)

 ----------------------------------------------------------------------------------------------------------------------

New Code: Using EOMONTH function, the expressions are simple now.

SELECT

EOMONTH(GETDATE(), -1) AS [Last Day Of Previous Month],

EOMONTH(GETDATE()) AS [Last Day Of Current Month],

EOMONTH(GETDATE(), 1) AS [Last Day Of Next Month]

Conclusion: Thanks to Microsoft SQL Server 2012 for making such frequently used expressions into a simple functions. Hope this helps! Happy TSQL'ing.

Sleep if file not available. Load if it is.

One of my old friends, asked me couple of days back how to design a process that will check for file existence in a folder. On finding the file the process should load the file else should sleep for 15 mins and then check for file existence. This process can be handled using advanced T-SQL batch that contains "WAITFOR" and "BCP" like below:

-- Code Starts here
DECLARE @File VARCHAR(500), @Sleeps INT, @FileAvailable INT, @Iteration INT

SET @FileAvailable = 0
SET @Iteration = 0
SET @Sleeps = 4
SET @File = 'C:\Data_DEPT.dat'

-- Check for File Existence
EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT

-- If the file is not available, then the process will wait (sleep) for specified time frame.
-- Here I am placing just 15 seconds time frame, but can you can specify anything.
WHILE @FileAvailable = 0 AND @Iteration < @Sleeps
  BEGIN
    SET @Iteration = @Iteration + 1
    WAITFOR DELAY '00:00:15'
    EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT
  END 

-- If the file is available, then load the file into DEPT2 table.
IF @FileAvailable = 1
BEGIN
DECLARE @BCP VARCHAR(500)
SET @BCP = 'bcp AdventureWorks2012.dbo.DEPT2 IN ' + @File + ' -f C:\Metadata_DEPT.fmt -S HPS\SQL -T'
EXEC MASTER..xp_cmdshell @BCP
END
-- Code Ends here

Execution of above code will result this:

Output of the data in table after file load:

Conclusion: I used two master stored procedures here "xp_FileExist" and "xp_cmdshell". The metadata file (format file) of the FlatFile is assumed to be predefined here. So similar structured files carrying different data can be processed. Hope this helps! Happy TSQLing ;)

Pyramid Structure using TSQL Code

Do you remember the college days, where one used write to simple C++ programs? Great days right? Today I thought of writing a such fun code to print a pattern. And this time using T-SQL.

T-SQL, not just programming business logic codes but also used to make fun. :)

Here is a small example of how we can use TSQL Code to draw a Pyramid Structure using Asterisk Symbol.

SET NOCOUNT ON;

DECLARE @T TABLE (ID VARCHAR(25))

DECLARE @SYM VARCHAR(1)
SET @SYM = '*'
DECLARE @I INT
SET @I = 1
DECLARE @J INT
SET @J = 6

INSERT INTO @T SELECT SPACE(@J) + @SYM
WHILE (@I <= @J)
BEGIN
DECLARE @SPACE INT
SET @SPACE = @J - @I
INSERT INTO @T
SELECT 
SPACE(@SPACE) + 
CASE 
WHEN @I = 1 THEN @SYM + @SYM
WHEN @I = 2 THEN @SYM + @SYM + @SYM
WHEN @I = 3 THEN @SYM + @SYM + @SYM + @SYM
WHEN @I = 4 THEN @SYM + @SYM + @SYM + @SYM + @SYM
WHEN @I = 5 THEN @SYM + @SYM + @SYM + @SYM + @SYM + @SYM
WHEN @I = 6 THEN @SYM + @SYM + @SYM + @SYM + @SYM + @SYM + @SYM
ELSE @SYM 
END + 
CASE 
WHEN @I = 1 THEN @SYM
WHEN @I = 2 THEN @SYM + @SYM
WHEN @I = 3 THEN @SYM + @SYM + @SYM
WHEN @I = 4 THEN @SYM + @SYM + @SYM + @SYM
WHEN @I = 5 THEN @SYM + @SYM + @SYM + @SYM + @SYM
WHEN @I = 6 THEN @SYM + @SYM + @SYM + @SYM + @SYM + @SYM
ELSE @SYM 
END + SPACE(@SPACE)
SET @I = @I + 1
END

SELECT * FROM @T;



On executing the above code you will find the Pyramid Structure. Happy TSQLing. ;)

Multiple Result Sets in single Stored Procedure

SQL Server 2012 introduces a new feature called "WITH RESULT SETS".
It makes the stored procedure returning multiple result sets. Lets see an illustration on how this new thing works.

Stored Procedure Code:
CREATE PROCEDURE dbo.usp_MultipleResultSets
AS
BEGIN
SET NOCOUNT ON;

-- Department Result Set
SELECT 
[DepartmentID]
,[Name]
,[GroupName] 
FROM [HumanResources].[Department]

-- Employee Result Set
SELECT 
[BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
FROM [HumanResources].[Employee]

END

Result Set:

EXEC dbo.usp_MultipleResultSets WITH RESULT SETS

(

(

DepartmentID smallint,

Name dbo.Name,

GroupName dbo.Name

),

(

[BusinessEntityID] [int],

[NationalIDNumber] [nvarchar](15),

[LoginID] [nvarchar](256),

[OrganizationNode] [hierarchyid],

[JobTitle] [nvarchar](50),

[BirthDate] [date],

[MaritalStatus] [nchar](1),

[Gender] [nchar](1),

[HireDate] [date],

[SalariedFlag] [dbo].[Flag],

[VacationHours] [smallint],

[SickLeaveHours] [smallint],

[CurrentFlag] [dbo].[Flag]

)

)

Hope it helps! Happy TSQLing!

Extract (Export) word document from SQL Server Table to a Windows Folder

In the last post, we have seen how to upload (insert) into a SQL Server Table. Now let's see how to extract it from the SQL Server table back to a folder.

Query:
-- Extracting the Resume from SQL Server Table to a folder location.
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'BCP "SELECT ResumeDoc FROM AdventureWorks2012.dbo.Consultants WHERE ConsultantName = ''Steve''" QueryOut "D:\Steve_Resume2.docx" -T -n -S HPS\SQL'
EXEC MASTER.dbo.xp_CmdShell @SQL

In above query, HPS\SQL is the SQL Server Instance.

Verifying in Folder Location:
SQL Server exported the word document into the specified location.

Note: By default, dbo.xp_CmdShell is disabled. In order to enable the stored procedure functionality, run the below code.

--Code Starts here:---->
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_CmdShell', 1
GO
RECONFIGURE
GO

Conclusion: For every import of word document, there exists an export, Just kidding ;) Hope this helps! Happy TSQLing :)

Upload (Insert) Resume (word document) to SQL Server Table

SQL Server's VARBINARY datatype allows us store BLOB data like Images, word documents etc., Lets see how that works with an example:

1.Create Table:
CREATE TABLE dbo.Consultants
(
ID INT IDENTITY(1,1),
ConsultantName VARCHAR(50),
ResumeDoc VARBINARY(MAX)
)

Once we have the table created, lets insert a resume located in some folder (D:\Steve_Resume.docx) into the table.

2. Query:

INSERT INTO dbo.Consultants (ConsultantName, ResumeDoc)

SELECT 'Steve', * FROM OPENROWSET (BULK N'D:\Steve_Resume.docx', SINGLE_BLOB) Doc

3. Check the Output:
SELECT ID, ConsultantName, ResumeDoc FROM dbo.Consultants

Hope this helps! Happy TSQLing !
Important: I will discuss how to extract the Resume from the SQL Server Table back to a windows folder location in the next post.

DML Trigger to track Updates on a Specific Column

DML observations are very common these days. Its more of auditing data changes made in a table. No one knowns when and who would insert, update or delete a record in the table. The same thing applies in business perspective as well.

In order to implement an automated process to handle the data changes made to the table, we use a database object trigger, a "DML Trigger".

Illustration:

E.g. I have a table named dbo.EMP which has a column JOB that needs to be audited.

To track down the Audit, we need one more table of below structure. Lets create it.

Now coming to the major part, the trigger creation. This is a DML Update Trigger on EMP table, which writes the changes information like which Employee record, ColumnName, Valid within what date range.

This is the initial View: No Audit records.

Changing designation of EMPNO 7499 from 'Salesman' to 'Manager'.

Here are the audit records after changes written to TrgAudit table.

Changing designation of EMPNO 7499 from 'Manager' to 'Sr. Manager'.

A new record is written to TrgAudit table, with previous record updated to Expired EndDate.

Lets cross check this with changes to some other column than JOB.
I have hiked an employee's salary, so updating the SALARY column as below.

You can notice that nothing is written to the Audit table because its not the JOB column which got updated. Hope this small example on DML Trigger helps you. Happy TSQLing :)