Dynamic execution query produces error when called from the procedure, but works when executed manually

Question

create procedure createTable(@tableName varchar(25), @columnName varchar(25), @columnType varchar(25))
as
    begin

    declare @sqlQuery as varchar(MAX)
    set @sqlQuery = 'create table ' + @tableName + '(' + @columnName + ' ' + @columnType + ' primary key)'

    print (@sqlQuery)
    exec @sqlQuery

    end
go

Above code produces below output and error:

create table proba2(ceva int primary key)
Msg 2812, Level 16, State 62, Procedure createTable, Line 9 [Batch Start Line 13]
Could not find stored procedure 'create table proba2(ceva int primary key)'.

Strangely, if I copy the first line in the output(which is the printed query itself) and execute it, it works; the table will be created. Nonetheless, the exec in the stored procedure does not. As far as I know, the exec call of the procedure should work. It should even work without specifying 'primary key' in the end of the string.

Answer 1

Replacing exec @sqlQuery with exec (@sqlQuery) must work. However, sp_executesql is recommended in your case as it has more flexibility.

Following is a typical example of sp_executesql:

Declare   @DatabaseName nvarchar(128) = 'MyDB'
        , @schema_name nvarchar(128) = 'dbo'
        , @table_name nvarchar(128) = 'MyTbl'
        , @column_name nvarchar(128) = 'MyTblColumn'
        , @ReadColumnInfo nvarchar(max)
        , @datatypename varchar(128);

select @ReadColumnInfo = 'Use ' + QUOTENAME(@DatabaseName) + '; ';
set @ReadColumnInfo =  @ReadColumnInfo + '
SELECT @datatype = TYPE_NAME(c.user_type_id)
FROM sys.objects AS o   
JOIN sys.columns AS c  ON o.object_id = c.object_id  
WHERE o.name = @TblName and o.schema_id = SCHEMA_ID (@scmName) and c.name = @columnName';


exec sp_executesql 
          @ReadColumnInfo -- Statement
        , N'@TblName varchar(128), @columnName varchar(128), @scmName varchar(128), @datatype varchar(128) OUTPUT' --- Params within Statement
        , @TblName = @table_name, @columnName = @column_name, @scmName = @schema_name, @datatype = @datatypename OUTPUT;  --- Param Values within Statement 
select @datatypename;