Category Archives: Sql Server 2012

Case Statement on Dynamically Generated Pivot Columns


Hi All,

I got into a situation where I have to apply case statement on Dynamically generated columns. Below is my requirement and how I how I achieved that.

I have created below result set with pivoting. Actually Server1,server2,server3 … are Rows and I am making them as columns. I am generating the servers information as columns dynamically

AssemblyName Server 1 Server 2 Server 3
Assembly1 NOT EXIST NULL NOT EXIST
Assembly2 NOT EXIST NULL NOT EXIST
Assembly3 NOT EXIST NULL NOT EXIST

I want the result like this.

AssemblyName Server 1 Server 2 Server 3
Assembly1 NOT EXIST EXIST NOT EXIST
Assembly2 NOT EXIST EXIST NOT EXIST
Assembly3 NOT EXIST EXIST NOT EXIST

Below is the solution for this.

–Below is the Dynamic SQL Pivot Script.

DECLARE @servers nvarchar(max)

SELECT @servers =

STUFF(

(

select distinct ‘,[‘ + S.ServerName + ‘]’

from tempassemblies S

for xml path(”)

),

1,1,”)

DECLARE @SQL nvarchar(max)

SELECT @SQL = N’

select

* into Temp_Assemblies

from (

SELECT p.AssemblyName, p.ServerName,p.CurrentStatus

FROM dbo.tempassemblies p

) Data

PIVOT (

max(CurrentStatus )

FOR [ServerName]

IN (

‘ + @servers + ‘

)

) PivotTable

exec sp_executesql @SQL

–update NULL values to ‘Custom Values’

DECLARE @dynamicsql nvarchar(MAX)

SELECT @dynamicsql= COALESCE(@dynamicsql + ‘ ‘, ”) +

‘UPDATE Temp_Assemblies SET ‘ + quotename(name)+ ‘ = ”EXIST” WHERE ‘ + quotename(name) + ‘ IS NULL ‘

FROM sys.columns WHERE object_id = object_id(‘Temp_Assemblies’)

EXECUTE (@dynamicsql)

Regards,

Chaitanya

Advertisements

Throw Statement in Sql server 2012


In 2012, raise error is replaced by throw statement

If we executed the below script, we will have the raise error statement that will catch error

Raise error have some limitation it will not flow the control to the catch block in some severities. Please execute the below query to see the limitation

For severity 10, it will not flow the control.

So we don’t have error raised.

Throw will eliminate these limitations.

Syntax of throw:

THROW [ { error_number | @local_variable },

{ message | @local_variable },

{ state | @local_variable } ]

[ ; ]

Parameters:

Error_number:

Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

The error_number parameter does not have to be defined in sys.messages

Message:

Is an string or variable that describes the exception. message is nvarchar(2048).

State:

Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

There is no severity parameter. The exception severity is always set to 16.

We have to specify throw statement inside begin try with parameters

We should not specify throw without parameters in try block

Throw statement should end with semi colon

%d bloggers like this: