Tags: Database , Stored Procedure

What is log file?
If you are experienced programmer then you know importance of log file. Beginner must understand the importance of log file. Log file is like light in the middle of dark jungle. Log file contains the information of any execution that you are working around. It doesn't matter you are working around computer or some machine in car manufacture unit.

Below example is for 'Microsoft SQL Server'. Change variables and input filename as per your requirement. If you want it in your stored procedure just append below code with in stored procedure.

------------------------------- At the starting of Stored Procedure -------------------------------
USE [NAME_OF_DATABASE]
GO

/*
 * Microsoft SQL Server
 * Stored Procedure : logExample.sql
 * Date of Creation : 1st Aug, 2013  
 * @author: Vicky Thakor
**/
/*
 * This option specifies the setting for ANSI NULL comparisons. 
 * When this is on, any query that compares a value with a null returns a 0. 
 * When off, any query that compares a value with a null returns a null value.
 **/
SET ANSI_NULLS ON
GO
/*
 * This options specifies the setting for usage of double quotation. 
 * When this is on, double quotation mark is used as part of the SQL Server identifier (object name). 
 * This can be useful in situations in which identifiers are also SQL Server reserved words.
 **/
SET QUOTED_IDENTIFIER ON
GO

/* Start: Log Creation Settings */
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
/* End: Log Creation Settings */
------------------------------- At the starting of Stored Procedure -------------------------------
------------------------------- With in Stored Procedure -------------------------------
/* Start: Log variables */
DECLARE @cmd sysname, @var sysname
DECLARE @LogFilePath varchar(255)
/* End: Log variable */

SET @LogFilePath = 'D:\SPLog\log_file_name.log'
SET @var = 'Line you want to store to file/Message like: Start execution';
SET @cmd = 'echo '+@var+' >> '+@LogFilePath
exec master..xp_cmdshell @cmd

SET @var = 'Step 2 and so on...';
SET @cmd = 'echo '+@var+' >> '+@LogFilePath
exec master..xp_cmdshell @cmd
------------------------------- With in Stored Procedure -------------------------------

Tags: Database , Stored Procedure