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

Lukasz Szozda · Accepted Answer · 2015-12-26T09:54:04.530

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

Excellent answer. Thanks. Is using `SYSNAME` instead of varchar for `@tableName` a better option? — Sunil, Dec 26 '15 at 15:35
@Sunil [SYSNAME](https://technet.microsoft.com/en-us/library/ms191240%28v=sql.105%29.aspx) is just alias for `NVARCHAR(128)`. I use it because it is a good practice, but varchar will also work(to 128 chars). — Lukasz Szozda, Dec 26 '15 at 16:51
I found `QUOTENAME` something new but very useful since it places square brackets around the table name. I have never come across this till your answer. Thanks. — Sunil, Dec 26 '15 at 18:13

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

1 Answers1