sp_hadr_isprimary

USE master;
GO

IF OBJECT_ID('dbo.sp_hadr_isprimary','P') IS NULL
   EXEC ('CREATE PROCEDURE dbo.sp_hadr_isprimary AS PRINT 1;');
GO

--return 1=current instance is primary, 0=current not primary
ALTER PROCEDURE dbo.sp_hadr_isprimary
( @agname sysname = 'AG_GROUP_NAME' )
AS
   DECLARE @PrimaryReplica sysname
         , @ThisReplica sysname
, @retval int = 0;
BEGIN
   SET NOCOUNT ON;
   SET @ThisReplica = cast(ServerProperty('ServerName') as sysname);

          SELECT @PrimaryReplica = hags.primary_replica
            FROM sys.dm_hadr_availability_group_states hags
      INNER JOIN sys.availability_groups ag
         ON ag.group_id = hags.group_id
           WHERE ag.name = @agname;

   IF UPPER(@PrimaryReplica) =  UPPER(@ThisReplica)
    BEGIN
      SET @retval = 1
    END

          SELECT @retval "IsPrimary"
               , @agname "AG_Name"
               , @PrimaryReplica "PrimaryReplica"
               , @ThisReplica "ThisReplica"
   RETURN @retval
END;
GO



Example Usage:

declare @retval int

exec @retval = master.dbo.sp_hadr_isprimary

SELECT @retval "return_value"