Doesn't work with Clustered instances. Or with default instances

SET @instanceName = CONVERT (VARCHAR,SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')) + '\' + CONVERT(VARCHAR,SERVERPROPERTY('InstanceName'))

Will return Null for a default instance and NULL out @instanceName

It will also return the physical node the SQL instance is running on in a cluster, which is not the name on the network to connect to.

Sadly this works better for clustered default instances

SET @instanceName = @@SERVERNAME + ISNULL('\' + CONVERT(VARCHAR,SERVERPROPERTY('InstanceName')),'')