Dynamic SQL parameter error, Incorrect syntax near '@myparametername'

Question

I'm building a fun stored procedure that will use dynamic SQL, sp_executesql with parameters, to allow some alter statements for a column in all database tables if the column name exists ( As you can see I used a cursor for loop all the tables on DB)

I built a test but the parameter doesn't work, I get the next error on each alter table statement that runs

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@parTablename'.

The next is the code

SET NOCOUNT ON;
GO
DECLARE @tablename varchar(100);
DECLARE @alteredColumn varchar(100)='[mycolumn] [datetimeoffset](0) NOT NULL;';
DECLARE @column varchar(100)='mycolumn';
DECLARE @parDefinition nvarchar(500) = N'@parTablename nvarchar(100)';
DECLARE @sqlCommand nvarchar(1000)= N'ALTER TABLE @parTablename ALTER COLUMN '+@alteredColumn;

DECLARE ALTERCURSOR CURSOR LOCAL FAST_FORWARD FOR
    SELECT name  AS tablename
    FROM sys.Tables

OPEN ALTERCURSOR;
FETCH NEXT FROM ALTERCURSOR INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
    --print @tablename
    IF EXISTS(SELECT *
        FROM   INFORMATION_SCHEMA.COLUMNS
        WHERE  TABLE_NAME = @tablename AND COLUMN_NAME = @column) 
    BEGIN
        EXECUTE sp_executesql @sqlCommand, @parDefinition,@parTablename = @tablename
    END
    FETCH NEXT FROM ALTERCURSOR INTO @tablename
END
CLOSE ALTERCURSOR;
DEALLOCATE ALTERCURSOR;
SET NOCOUNT OFF;
GO

SOLUTION
Apparently is not possible to send a table name as a parameter, instead of that I used the @SeanLange option for degub with a little modification
SET @sqlCommand =Replace(@sqlCommand, '@parTablename',QUOTENAME(@tablename)) EXECUTE sp_executesql @sqlCommand

I replaced the sql command variable to 'PRINT @parTablename ' and i can see all the table names, i don't get why isn't work with the alter statements — Eduardo Chávez
what's the @partablename = @tablename for? and where's @partablename defined? you have a string that contains a definition for it, but that string's never executed. — Marc B
Is used @partablename = @tablename for set the value of @partablename That is defined here DECLARE @parDefinition nvarchar(500) = N'@parTablename nvarchar(100)'; And used here EXECUTE sp_executesql @sqlCommand, @parDefinition,@parTablename = @tablename @Marc Check the documentation ;) link Note that @tablename is set on the cursor — Eduardo Chávez
You can't pass the table name as a parameter, you need to build the string manually. — Aaron Bertrand
Why did you use concatenation for the "alteredColumn" variable, but parameterization for the "tablename" variable? — Tab Alleman

Sean Lange Sean Lange · Accepted Answer · 2016-06-22T16:46:37

You can't stick a parameter in the middle of your dynamic sql like this. You need to use PRINT instead of EXECUTE to debug this. I wouldn't use a cursor for this myself but if you go that path you will have to do something like this before the EXECUTE statement.

Set @sqlCommand = Replace(sqlCommand, '@parTablename', @parTablename)

Dynamic SQL parameter error, Incorrect syntax near '@myparametername'

1 Answers