sp_executesql not setting a variable correctly in a dynamic sql query in SQL Server 2012

Question

In query below, I am trying to set the value of @productsExist using a dynamic query that is executed by sp_executesql in SQL Server 2012. The problem is that even though the table @tableName exists and contains records, the value of productsExist is always null after the dynamic query is executed.

Question: Why is the query returning null for @productsExist even when the the table exists and has records?

DECLARE @productsExist INT;
DECLARE @countQuery NVARCHAR(MAX) = 'IF OBJECT_ID(@tableName, N''U'') IS NOT NULL 
                     begin  select top(1) @productsExist = 1  from ' + @tableName + ' end';

EXECUTE sp_executesql @countQuery, N'@tableName varchar(500),@productsExist INT',
              @tableName = @tableName,
              @productsExist = @productsExist;

select @productsExist as ProductsExist--returns always a NULL value for ProductsExist

Answer 1

You need to declare @productsExist parameter as OUTPUT:

[ OUT | OUTPUT ]

Indicates that the parameter is an output parameter

DECLARE @productsExist INT
        ,@tableName SYSNAME = 'tab';

DECLARE @countQuery NVARCHAR(MAX) = 
N'IF OBJECT_ID(@tableName, N''U'') IS NOT NULL 
  begin  select top(1) @productsExist = 1  from ' + QUOTENAME(@tableName) + ' end';

EXECUTE dbo.sp_executesql 
        @countQuery,
        N'@tableName SYSNAME ,@productsExist INT OUTPUT',     -- here
        @tableName = @tableName,
        @productsExist = @productsExist OUTPUT;               -- here

SELECT @productsExist as ProductsExist;

SqlFiddleDemo

---

If there is no records in specified table the @productsExist will return NULL. If you want 1 for existing and 0 for no records use:

DECLARE @countQuery NVARCHAR(MAX) = 
N'IF OBJECT_ID(@tableName, N''U'') IS NOT NULL 
  BEGIN
    IF EXISTS (SELECT 1 FROM '+ QUOTENAME(@tableName) + ')
       SELECT @productsExist = 1
    ELSE 
       SELECT @productsExist = 0
  END'; 

SqlFiddleDemo2

Result:

table not exists          => NULL
table exists no records   => 0
table exists >= 1 records => 1