NOT EXISTS might also be an option here.

I don't see anything in the original code that indicates that Blocked or Doc Type must match in the CR Log, but naturally adjust the code as needed to match your requirements.

CREATE PROCEDURE [dbo].[USPT]

@CompanyName varchar(50),

@tablename varchar(50),

@print_sql bit = 0,

@exec_sql bit = 1

AS

SET NOCOUNT ON;

-- EXEC [USPT] 'xyz corp','Sales Header',1,0

DECLARE @str1 varchar(MAX)

SET @CompanyName = PARSENAME(@CompanyName, 1)

SET @tablename = PARSENAME(@tablename, 1)

--validate input param values

IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE name LIKE @CompanyName + '%' AND type IN ('IF', 'TF', 'U', 'V'))

BEGIN

RAISERROR('Company name is not valid, invalid access attempt was logged!', 16, 1)

--log access attempt

RETURN -1

END --IF

IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE name = @tablename AND type IN ('IF', 'TF', 'U', 'V'))

BEGIN

RAISERROR('Table name is not valid, invalid access attempt was logged!', 16, 1)

--log access attempt

RETURN -2

END --IF

SET @str1 = '

SELECT @CompanyName AS Company, COUNT(*) as SalesCount

FROM ['+@CompanyName+'$'+@tablename+'] tbl

WHERE

tbl.[CR Blocked] = 1 AND

tbl.[Document Type] IN (1,4,5) AND

NOT EXISTS(SELECT 1 FROM ['+@CompanyName+'$CR Log] WHERE CRStatus = 2 AND No_ = tbl.No_)

'

IF @print_sql = 1

PRINT @str1

IF @exec_sql = 1

EXEC (@str1)

GO --end of proc