0
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.

2

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;
  • Thank you very much! This solved my problem. Thank you for the extra example, also. – M.Ionut Nov 3 '20 at 8:48
  • 1
    Regardin sp_executeql, I am aware of its benefits. In one of my PDF files from school I saw something like " If parameters also have to be used for columns or table names, this only works with EXEC". In my given example this is not the case, but in my whole project it is. I wanted to keep it consistent. This is why I didn't use sp_executesql, though I am aware of its advantages that I've seen mentioned in the same PDF, such as: - sometimes much faster than EXEC - prevents SQL injection - better choice if using dynamic SQL regularly, because the query plan / execution plan can be reused – M.Ionut Nov 3 '20 at 8:57
  • @M.Ionut, Good you catch that without being mentioned in my answer :-) – Shekar Kola Nov 3 '20 at 9:32
  • thank you again, kind sir! – M.Ionut Nov 3 '20 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.