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. ;)

Best Practices for Stored Procedure

Stored Procedure: It is considered as one of the most optimized database object in SQL Server database. If we implement few best practices, it can give more efficient results.

Best Practices: 

1. Use "SET NOCOUNT ON" as the first line of the stored procedure body.
2. Do not use SP_ or sys_ prefixes for naming user stored procedures.
3. Always include schema names prefix for stored procedure and other database objects used in stored procedure. It reduces processing time.
4. Avoid 'SELECT *' and specify the columns in SELECT statement.
5. Use explicit transactions by using BEGIN/END TRANSACTION.
6. Use TRY/CATCH for Error Handling.
7. Defined NULL or NOT NULL for Temporary Table Columns.
8. Last but not least implement best practices for the queries used in Stored Procedure.

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.