IF ELSE BLOG IN SQL

Blue print of IF ELSE:

IF Boolean_expression
     { sql_statement | statement_block }
[ ELSE
     { sql_statement | statement_block } ]

Original blue print:

IF (condition)
BEGIN
.........................................
END

ELSE
.........................................


Sample 1:

Declare @selecdate datetime

IF (Select top 1 salarydate from employee where empid= 'EP00001')  is not null

BEGIN

select top 1 @selecdate= salarydate from employee where empid= 'EP00001'

END

ELSE

Select top 1 @selecdate= date from salarypayslip where empid= 'EP00001' and month='Jan'

select @selecdate

SELECT TOP 1 Value in Variables

Hi,

declare @FirstLocationNotZero int
select @FirstLocationNotZero = top 1 Location from dbo.Table1--------------see wrong one
where value<>0
print @FirstLocationNotZero

I should obtain 2, instead of:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'top'

Solution:

declare @FirstLocationNotZero int
select top 1 @FirstLocationNotZero = Location ------------------------> see Right one
from dbo.Table1
where value<>0

print @FirstLocationNotZero 

Difference between SQL Server 2008 and SQL Server 2012

Difference between SQL Server 2008 and SQL Server 2012

SQL Server 2008	SQL Server 2012
The Maximum number concurrent connections to SQL Server 2008 is 32767.	SQL server 2012 has unlimited concurrent connections.
The SQL Server 2008 uses 27 bit bit precision for spatial calculations.	The SQL Server 2012 uses 48 bit precision for spatial calculations
TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008	TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012	ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire set In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time. SELECT BusinessEntityID, FirstName, LastName FROM Person.Person ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
SQL Server 2008 is code named as Katmai.	SQL Server 2012 is code named as Denali
In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition.	In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server.
Sequence is not available in SQL Server 2008	Sequence is included in SQL Server 2012.Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence. /****** Create Sequence Object ******/ CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; /****** Create Temp Table ******/ DECLARE @Person TABLE ( ID int NOT NULL PRIMARY KEY, FullName nvarchar(100) NOT NULL ); /****** Insert Some Data ******/ INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Mohamed Iqbal'); /****** Show the Data ******/ SELECT * FROM @Person; The results would look like this: ID FullName 1 Umar Ali 2 John Peter 3 Mohamed Iqbal
The Full Text Search in SQL Server 2008 does not allow us to search and index data stored in extended properties or metadata	The Full Text Search in SQL Server 2012 has been enhanced by allowing us to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.
Analysis Services in SQL Server does not have BI Semantic Model (BISM) concept.	Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes: Data Model Business Logic Data Access BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.

SQL Server Services Status

SQL Server Services Status











--Check SQL Server Services Status
SET NOCOUNT ON

CREATE TABLE tempdb.dbo.RegResult
   (
   ResultValue NVARCHAR(4)
   )
CREATE TABLE tempdb.dbo.ServicesServiceStatus 
   (
   RowID INT IDENTITY(1,1)
   ,ServerName NVARCHAR(128)
   ,ServiceName NVARCHAR(128)
   ,ServiceStatus VARCHAR(128)
   ,StatusDateTime DATETIME DEFAULT (GETDATE())
   ,PhysicalSrverName NVARCHAR(128)
   )
DECLARE
    @ChkInstanceName NVARCHAR(128)   /*Stores SQL Instance Name*/
   ,@ChkSrvName NVARCHAR(128)        /*Stores Server Name*/
   ,@TrueSrvName NVARCHAR(128)       /*Stores where code name needed */
   ,@SQLSrv NVARCHAR(128)            /*Stores server name*/
   ,@PhysicalSrvName NVARCHAR(128)   /*Stores physical name*/
   ,@DTS NVARCHAR(128)               /*Store SSIS Service Name */
   ,@FTS NVARCHAR(128)               /*Stores Full Text Search Service name*/
   ,@RS NVARCHAR(128)                /*Stores Reporting Service name*/
   ,@SQLAgent NVARCHAR(128)          /*Stores SQL Agent Service name*/
   ,@OLAP NVARCHAR(128)              /*Stores Analysis Service name*/
   ,@REGKEY NVARCHAR(128)            /*Stores Registry Key information*/

SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128)) SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL        /*Detect default or named instance*/BEGIN
   SET @TrueSrvName = 'MSSQLSERVER'
   SELECT @OLAP = 'MSSQLServerOLAPService'  /*Setting up proper service name*/
   SELECT @FTS = 'MSFTESQL'
   SELECT @RS = 'ReportServer'
   SELECT @SQLAgent = 'SQLSERVERAGENT'
   SELECT @SQLSrv = 'MSSQLSERVER'END
ELSE
BEGIN
   SET @TrueSrvName =  CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
   SET @SQLSrv = '$'+@ChkSrvName
   SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/
   SELECT @FTS = 'MSFTESQL' + @SQLSrv
   SELECT @RS = 'ReportServer' + @SQLSrv
   SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
   SELECT @SQLSrv = 'MSSQL' + @SQLSrvEND

/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of SQL Sever service*/
   EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of SQL Agent service*/
   EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus  SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END

/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of SQL Browser Service*/
   EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Integration Service Section ----------------------------------------------*/
IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Intergration Service*/
   EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@DTS
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Reporting Service Section ------------------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Reporting service*/
   EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@RS
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Analysis Service Section -------------------------------------------------*/IF @ChkSrvName IS NULL        /*Detect default or named instance*/
   BEGIN
   SET @OLAP = 'MSSQLServerOLAPService'END
ELSE
   BEGIN
   SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
   SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAPEND

INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Analysis service*/
   EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
   INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Full Text Search service*/
   EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
   INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
   UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
   TRUNCATE TABLE tempdb.dbo.RegResult
END
/* -------------------------------------------------------------------------------------------------------------*/SELECT  ServiceName AS 'SQL Server Service'
   ,ServiceStatus AS 'Current Service Status'
   ,StatusDateTime AS 'Date/Time Service Status Checked'FROM tempdb.dbo.ServicesServiceStatus

/* -------------------------------------------------------------------------------------------------------------*/

/* --Send DB Mail - Uncomment this section if you want to send email of the service(s) status

EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLAdmin',
@recipients='pearlknows@yahoo.com',
@subject='SQL Service(s) Status Update',
@body='This is the latest SQL Server Service(s) Status Report. Please review and take appropriate action if necessary:',
@query='SET NOCOUNT ON SELECT  ServiceName AS ''SQL Server Service''
   ,ServiceStatus AS ''Current Service Status''
   FROM tempdb.dbo.ServicesServiceStatus'
*/
DROP TABLE tempdb.dbo.ServicesServiceStatus    /*Perform cleanup*/DROP TABLE tempdb.dbo.RegResult